SSAS DAX Grand Total not matching with actual Sum

0
votes

I have a fact which has relationships with 2 dimension tables.

  1. Direct relationship
  2. Through Bridge table

enter image description here

I have created a sum measure that should sum up the Amount column.

I have created two queries that don't give me the expected result

Query 1:

Sum of Amount:= CALCULATE(SUM(Fact[Amount]))

Current result : The Grand total is 600

enter image description here

Query 2:

Sum of Amount:= IF(HASONEVALUE('Dimension 1'[value]),CALCULATE(SUM(Fact[Amount])),
SUMX(VALUES('Dimension 1'[value]),CALCULATE(SUM(Fact[Amount]))))

Current result : The Grand Total is 400

enter image description here

Expected result : When the Dimension 1 is selected, the grand total should be 400 and when the Dimension 2 selected the Grand total should be 600.

enter image description here

Could anyone please help me to achieve this?

1
daxssas-tabulardaxstudio

1 Answers

0
votes

The answer is that it is not possible to have a measure that behaves differently on the totals when in the visual a different dimension is selected on the rows.

This happens because when evaluating the totals there is no filter on the dimension.

It would be like having a measure that can give two different results when no dimension is selected, for instance in a card visual.

SO answer is to implement two separate measures.

Otherwise to select the behavior using a slicer on a parameter table, or to use a calculation group.