Summing the displayed results of a measure

[jeffreyweir]

I'm trying to work out how to iteratively sum a Measures column in a PowerPivot table, so that the Grand Total that appears for the Median Weighted Gender Gap column is in fact the Sum of the displayed values for that column.

...but my DAX brain is far from fully formed, and I can't conceptualise how to do this. It seems in my mind that I need to use some combination of IF(HASONEVALUE to determine if the calculation is for a Grand Total or not, and then some kind of X iterator. But I've been playing with random stuff for a while and have had no joy.

The relevent measures I have are:

Median Weighted Gender Gap = [Female Distribution]*[Gender Gap (Median)]

Female Distribution = [Female]/[Female Count]

Gender Gap (Median) =IF(AND([Median Salary (Female)]>0,[Median Salary (Male)]>0),1-DIVIDE([Median Salary (Female)],[Median Salary (Male)],BLANK()),BLANK())

Female Count =CALCULATE([Female],ALLSELECTED(OrgChart_Extractdata[Pay_Grade]))

Median Salary Female =CALCULATE(MEDIAN(OrgChart_Extractdata[Salary_Amount]), OrgChart_Extractdata[Gender]="Female")

Median Salary Male =CALCULATE(MEDIAN(OrgChart_Extractdata[Salary_Amount]), OrgChart_Extractdata[Gender]="Male")

Looking forward to having someone point out the blindingly obvious to me, as I'm completely stumped!

[jeffreyweir]

Note that I've looked at articles such as Matt Allington's When to use SUM vs SUMX in DAX, but I'm trying to iterate over two measures being multiplied, and they in turn are based on other measures.  Whereas Matt's article shows me something much simpler: how to wrap a couple of tables in a SUMX, like so (using his example):

Avg Visits per Day :=
IF (
    HASONEVALUE ( Sales[Cust Number] ),
    DIVIDE ( [Visits], [Count of days] ),
    DIVIDE (
        SUMX ( VALUES ( Sales[Cust Number] ), [visits] ),
        SUMX ( VALUES ( Sales[Cust Number] ), [Count of days] )

[jeffreyweir]

This shows that my attempt at using SUMX on measures is the wrong approach: as you can see, I simply can't add those measures because they don't appear in the intellisence.

The best I've managed is to simply suppress the Grand Total like so:

=IF(HASONEVALUE(OrgChart_Extractdata[Pay_Grade]),[Female Distribution]*[Gender Gap (Median)],BLANK())

[mattbrice · Accepted solution]

From your comments are you saying you have tried this and it didn't work?

Median Weighted Gender Gap =
SUMX (
    VALUES ( OrgChart_Extractdata[Pay Grade] ),
    [Female Distribution] * [Gender Gap (Median)]
)
 

[jeffreyweir]

Ahhh....that's what I want. I had the order of the SUMX arguments transposed. Man, I feel like a fish out of water with DAX. All those years mastering Excel formulas to the highest degree, and then I screw up on the simplest stuff in DAX! Thank you very, very much for putting me on the right path 🙂