Stuck with simple aggreagte of aggregates

[Anonymous]

I'm trying to do a simple calculation but stuck on aggregating data that is already aggregated.
I have a raw table of multiple rows per Pupil, with a value for each Test they've say.

I've added a MEASURE:
AvRes=Average(TEST[result])

And so in a table viz, can show PUPIL and AvRes.

I'm working towards a correlation coefficient so now want to Sum the AvRes for all the selected* Pupils (*they may have been filtered).
How can I add a measure to the table showing the SUM of all the AvRes values (for the selected pupils - ie. the ones in the table viz).  This must be simple, but I'm stumped.

[DataInsights]

@Anonymous, I think this is the DAX pattern you want. If the output isn't what you're envisioning, we can tweak it to meet the requirements.

Measure:

Sum AvRes = 
VAR vTestResultTable =
    ADDCOLUMNS ( VALUES ( TestResult[Pupil] ), "AvRes", [AvRes] )
VAR vResult =
    SUMX ( vTestResultTable, [AvRes] )
RETURN
    vResult

[Anonymous]

Thanks for your reply - looks like I'm nearly there...

Your formula works fine when the measure is dragged onto a card (i.e. a summary), but isn't summarizing when added to a table viz.

I need to be able to display like this:

Pupil	AvRes (measure)	SumAvRes
Person1	82	162
Person2	80	162

But currently, that formula will group by the row, so it just duplicates the AvRes value - like this:

Pupil	AvRes (measure)	SumAvRes
Person1	82	82
Person2	80	80

I tried using ALLSELECTED:

VAR vResult = SUMX ( ALLSELECTED(vTestResultTable), [AvRes] )

but that expects a table reference.

So close.... 

[DataInsights]

@Anonymous, you're on the right path with ALLSELECTED. The missing piece was CALCULATE, which is necessary to override the filter context coming from the Pupil field in the table visual.

Sum AvRes = 
VAR vTestResultTable =
    ADDCOLUMNS ( ALLSELECTED ( TestResult[Pupil] ), "AvRes", [AvRes] )
VAR vResult =
    CALCULATE (
        SUMX ( vTestResultTable, [AvRes] ),
        ALLSELECTED ( TestResult[Pupil] )
    )
RETURN
    vResult

[Anonymous]

Thanks again...but this still isn't working for me in a Table viz, I'm getting the following with the new measure:

Pupil	AvRes	Sum AvRes
Person1	82	82
Person2	80	80

It works a treat when I add the new measure to a card, but in a table (and therefore in any further calcs) it's not applying to the WHOLE table for some reason.  This is baffling me.  

[DataInsights]

@Anonymous, is the same Pupil column being used in both the slicer and table visual? Could you upload a pbix so I can troubleshoot?

[Anonymous]

So, I've got this working (using your suggested solution (thank you).

I stripped it back to a handful of rows and a single table, and it worked fine, which I couldn't understand.

Then I realised a subtle difference to how I was set up.

Previously "Pupil" was in a related Table, not in the source table for the Measure:

Is there some logic behind this ?

[Anonymous]

And...even is I add a calculated column relPupil=Related(Pupil[Pupil]), so it is in the source, I get the same problem.  Which would suggest all the normalised data needs to be in the source table.  That seems kind of limiting?  Any way roundf this?

[richbenmintz]

Hi @Anonymous,

Using a Star Schema is the best approach with Power BI, you would just need change the table you are iterating through with your SUMX. Assuming a Pupil Table and Results table the following should provide the desired results.

Avg Results = AVERAGE(Results[Score])

Sum of Averages = CALCULATE(SUMX('Pupil', [Avg Results]), ALLSELECTED(Pupil))

Hope this Helps,
Richard

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!