Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

How to get Grand Total on Measure Table

Hi, The Grand Total of each row in the measure table  is incorrect. How can I fix it? Sample here.    
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , Try calculations like

 

Values for Matrix = 
VAR Calc = 
IF(SELECTEDVALUE('Measure'[ID])=1,FORMAT([Current Hours],"#,0"),
IF(SELECTEDVALUE('Measure'[ID])=2,FORMAT([Available Hours],"#,0"),
IF(SELECTEDVALUE('Measure'[ID])=3,FORMAT([Diff (Hours)],"#,0"),
IF(SELECTEDVALUE('Measure'[ID])=4,FORMAT([Current Technician],"#0"),
IF(SELECTEDVALUE('Measure'[ID])=5,FORMAT('Available Technician'[Available Technician],"#0"),
FORMAT([Diff (Technician)],"#0"))))))
VAR Calc1 = 
sumx(VALUES('Measure'[Measure]),CALCULATE(IF(SELECTEDVALUE('Measure'[ID])=1,[Current Hours],
IF(SELECTEDVALUE('Measure'[ID])=2,[Available Hours],
IF(SELECTEDVALUE('Measure'[ID])=3,[Diff (Hours)],
IF(SELECTEDVALUE('Measure'[ID])=4,[Current Technician],
IF(SELECTEDVALUE('Measure'[ID])=5,'Available Technician'[Available Technician],
[Diff (Technician)])))))))
return
if(ISINSCOPE('Measure'[Measure]),Calc,Calc1)
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi amitchandak, it doesn't work.

    Do you mind to share your pbix file for the above calculations?

     

     

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi amitchandak , thanks for the file.

        The Total highlighted in red is still incorrect. 

        E.g.

        Total for Available Hours should be 1720 instead of 860.

        Total for Current Technician should be 34 instead of 13.