Getting totals to add up

[s--turn]

Hello,

Due to an unavoidably complex data structure, I'm struggling to work out how to calculate totals and subtotals here.

The table aims to show, for each country (e.g. Uganda) and for each grade of membership (e.g. Fellow, Graduate, Member), how many members are on each price plan ("#Members"), and how much we are "giving away" in discounts.  The "No Discount / 2025" price plan is the full price, hence £0 for those rows (i.e. we are not giving any money away).  For the other price plans, the amount in the middle column (Amount Given Away) shows how much of a discount a single member gets on the full price - e.g., the full price for a Fellow being £499, someone on the "Free / 2025" price plan for that grade is getting a £499 discount.

As you can see, the "Amount Given Away" column for the country is totalling £614.20 - a meaningless figure, as that is simply the sum total of the possible discounts of £499 and £115.20 listed.  There is no meaningful total I would like to see here - I'd rather it was blank.

The Total Amount Given Away column is simply [#Members] * [Amount Given Away], and therefore also comes up with an incorrect country total of £3,685.20 (i.e. 6 x £614.20).  What I'd rather see here is 499 + 115.2 + 0 + 0 = £614.20. (It's coincidence that this is the same figure we saw in the previous column - or rather, it's because there is only one person getting each discount in Uganda... this would not be the same for all countries)

I can see why this is going wrong, but I can't figure out how to do it differently.  Help!

Thank you.

[DataNinja777 · Accepted solution]

Hi @s--turn 

You're very close to the right solution — the main issue is that DAX totals behave differently than expected because they evaluate across a broader context than the row level. For your Amount Given Away measure, you're seeing the wrong total (£614.20) because it's just summing the per-member discounts without considering whether it's actually at the row level. To fix this, you can use the ISINSCOPE function to make sure the measure only shows values when a specific pricelevel[Name] is in scope. When it's not (i.e., at subtotal or total levels), we return blank. Here's the revised version of that measure:

Amount Given Away = 
IF(
    ISINSCOPE(pricelevel[Name]),
    SUM(productpricelevel[Discount on Price]),
    BLANK()
)

Now for the Total Amount Given Away measure — this one should calculate the number of members for each price level and multiply that by the amount given away per member. To get that, use a SUMX over the list of price levels in the current context. That way, it respects the grouping (e.g., per grade or per country) and correctly aggregates only where it makes sense. Here's the DAX formula:

Total Amount Given Away = 
SUMX(
    VALUES(pricelevel[Name]),
    [# Members] * [Amount Given Away]
)

This gives you the right total for each grade and country: it multiplies how many members are in each pricing tier by their individual discount, and adds that up. If you want to suppress totals at the country level (like Uganda) or higher rollups, you could optionally wrap the whole measure in another IF using ISINSCOPE to only return values when you're in a grade or price level context. But for most cases, the above setup will do exactly what you're aiming for — blank "Amount Given Away" at higher levels and a properly calculated "Total Amount Given Away" that adds up per-member discount values.

Best regards,

[s--turn]

Hi @DataNinja777 , thanks so much!  It's not quite doing what it should, I don't think (or maybe I am missing something).  Here are your two measures.  The "Amount Given Away v2" seems to work like a charm.  However, the "Total Amount Given Away v2" seems to fall apart at the country level.  I can't work out where it's getting that £1,731.20 total from.  Any explanation much appreciated!

In case it helps, this is the productpricelevel table where that "Discount on Price" field comes from:

[s--turn · Accepted solution]

Aha... I may have solved it... see below:

[v-kathullac]

Hi @s--turn ,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

If the given response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

Chaithanya.

[s--turn]

Just to update, I have partly solved this (see below, I have created a new measure called Discount to replace the Amount Given Away one), but am still stuck on how to generate a country total.

[DataNinja777 · Accepted solution]

Hi @s--turn 

You're very close to the right solution — the main issue is that DAX totals behave differently than expected because they evaluate across a broader context than the row level. For your Amount Given Away measure, you're seeing the wrong total (£614.20) because it's just summing the per-member discounts without considering whether it's actually at the row level. To fix this, you can use the ISINSCOPE function to make sure the measure only shows values when a specific pricelevel[Name] is in scope. When it's not (i.e., at subtotal or total levels), we return blank. Here's the revised version of that measure:

Amount Given Away = 
IF(
    ISINSCOPE(pricelevel[Name]),
    SUM(productpricelevel[Discount on Price]),
    BLANK()
)

Now for the Total Amount Given Away measure — this one should calculate the number of members for each price level and multiply that by the amount given away per member. To get that, use a SUMX over the list of price levels in the current context. That way, it respects the grouping (e.g., per grade or per country) and correctly aggregates only where it makes sense. Here's the DAX formula:

Total Amount Given Away = 
SUMX(
    VALUES(pricelevel[Name]),
    [# Members] * [Amount Given Away]
)

This gives you the right total for each grade and country: it multiplies how many members are in each pricing tier by their individual discount, and adds that up. If you want to suppress totals at the country level (like Uganda) or higher rollups, you could optionally wrap the whole measure in another IF using ISINSCOPE to only return values when you're in a grade or price level context. But for most cases, the above setup will do exactly what you're aiming for — blank "Amount Given Away" at higher levels and a properly calculated "Total Amount Given Away" that adds up per-member discount values.

Best regards,