Measures Total not Summing

[PShingadia]

Hi All:

I am relatively new to DAX and having issues arriving at a total for a measure. The measure is:

Result:=DIVIDE([Value], [Factor])

[Value] and [Factor] are two measures calculated in the model and work fine and trying to achieve Result by dividing one by the other. Result total shows at 221 with row context applying but need it to show actual sum which is 59.



[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="class: xl63, width: 64"]Value[/TD]
[TD="class: xl63, width: 64"]Factor[/TD]
[TD="class: xl63, width: 64"]Result[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl64"] 260.97[/TD]
[TD="class: xl68"] -[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl66"] 190[/TD]
[TD="class: xl64"] 31.66[/TD]
[TD="class: xl68"] 6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl66"] 2[/TD]
[TD="class: xl64"] 5.91[/TD]
[TD="class: xl68"] 0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl66"] 14[/TD]
[TD="class: xl64"] 14.71[/TD]
[TD="class: xl68"] 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl66"] 14[/TD]
[TD="class: xl64"] 0.29[/TD]
[TD="class: xl68"] 49[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl70"] 1[/TD]
[TD="class: xl71"] 0.38[/TD]
[TD="class: xl72"] 3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Total[/TD]
[TD="class: xl67"] 221[/TD]
[TD="class: xl65"] 1.00[/TD]
[TD="class: xl69"] 221[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance for any help

 

[gazpage]

Is that literally how your output table looks? Nothing in the rows section? If so and each of the lines in your data are like the above then you want something like this.

Output:=
SUMX (
    Table,
    [Result]
)

If you do have something in the rows section, say it is 'Category' then you want the following instead:

Output:=
SUMX (
    VALUES ( Table[Category] ),
    [Result]
)

 

[PShingadia]

gazpage:

Thanks for the quick reply. Have spent nearly a day trying to solve this so at wits end!

I tried your suggestion but it returns blank as an answer and don't understand why. These are all measures - not sure if that makes any difference.

 

[PShingadia]

gazpage:

Sorted out the blank issue. I was selecting wrong 'Category' in my table but when I changed to another column then your suggestion did produce a total.

However this measure is drives a pivot table and slicer on a dashboard. Is there a way to allow this selected 'Category' column in the DAX formula to be dynamic so the column changes as the user selects different items on the slicer?

Thanks again and owe you big for help already!!

 

[gazpage]

This is a big weakness in Power BI in my opinion, probably the most glaring omission in my opinion. See below for a work around.

http://leanx.eu/tutorials/dynamic-dimensions-in-power-bi

<strike></strike>Otherwise you can do it via bookmarking, basically create multiple charts, one for each category. Then use the bookmarks to only show which ever graph you want currently.

 

[PShingadia]

I had a feeling this was the case hence spending all day on finding a solution. Looks like I may have to create a different measure for each option.

Appreciate the help today - thanks!!!