SSAS Cube Calculated Member infinity Totals value

Question

I'm working on Power BI with a live connection SSAS cube. I have two dimension tables, Customer and Year and a fact table. I want to generate a report which shows the proportion of sales amount by citizenship for each Year. I used this MDX expression on SSAS Cube to create a new calculated member.

(([Year].[Year].currentmember,[Dim Customer].[Cittzenship].currentmember),[Measures].[Totale Sales]) /([Cittzenship].currentmember.parent,[Measures].[Totale Sales])

The result of the query works as expected but on power BI report table the total value is infinity. Result

I'm stuck solving this problem. I would love if somebody guide me how to solve this.

Answer 1

The reason is probably that for the totals, the parent does not exist, and hence, its value is replaced by null for the calculation. In most contexts, SSAS replaces null by 0, and hence the division is by 0, which mathematically is infinity. What you can do is use the Divide function instead of /, which is exactly meant for this purpose, as it delivers the same result as /, except if the divisor is zero, in which case it delivers null.

By the way: Divide was introduced in SSAS 2012, so if you are not on that version at least, you would have to use a construct like IIf(divisor <> 0, dividend / divisor, null), which is exactly how Divide(dividend, divisor) is defined. Like I said above, in MDX in contrast to SQL, comparing to 0 also implicitly compares to null.