How to display subgroup totals in a table visual in Power BI?

Question

I need to display subgroup totals in a table visual in Power BI. However, Power BI is showing me incorrect totals. How do I create a measure to show this?

My current table visual is:

My table relationships are:

What I need is:

SiteName          LiftName           Amount
Site 1            Lift 1             668.00
Site 1            Lift 2             668.00
Site 2            Lift 3             604.00
Site 3            Lift 4             9.54
Site 4            Lift 6             9622.50
Site 4            Lift 8             9622.50
Site 4            Lift 9             9622.50

I have tried adding an inactive relationship and using that with a USERELATIONSHIP function, but it gives me the same as what I have already:

SiteAmount = calculate(sum(Sales[Amount]),USERELATIONSHIP(Sales[SiteID],Sites[SiteID]))

Is there any way to get this table to display as needed? I cannot amend the relationships in the model to link Sales table with Sites table directly. See below PBIX if needed:

https://1drv.ms/u/s!AuiIgc_S9J5JhbYbCO2jbeQPfpzmXw

Cheers

Ryan B. Ryan B. · Accepted Answer · 2019-04-12T15:20:53

What you want, it seems to me, is the sum of [Amount] without any filtering except for Site. DAX provides the ALLEXCEPT function to do this kind of filtering.

Unfortunately, the field that whose influence we're trying to ignore is part of the relationship to the tables in the visual. So we get some weirdness and I think it's unavoidable. We can ask DAX to hide those rows as a second step.

SiteAmount = 
Var Amount = CALCULATE(
    SUM(Sales[Amount])
    , ALLEXCEPT(Sales,Sites[SiteName]
))
RETURN IF(ISBLANK(SUM(Sales[Amount])),BLANK(), Amount)

How to display subgroup totals in a table visual in Power BI?

1 Answers