Measure for Context Based Sales Calculation Not Showing Correct Total

[vm8181]

Hi everyone,

I am facing an issue with a dax measure in Power BI that calculates sales based on different attributes from multiple tables. The measure works correctly at the individual row level but does not show the correct total.

Measure i am Using:
Sales =
VAR _values =
SWITCH(
TRUE(),
ISINSCOPE(City[city_name]),  SUM(City[city_revenue]),
ISINSCOPE(Keyword[keyword_match_type]), SUM(Keyword[keyword_revenue]),
ISINSCOPE(Keyword[keyword_name]), SUM(Keyword[keyword_revenue]),
ISINSCOPE('KW Master'[keyword_type]), SUM(Keyword[keyword_revenue]),
ISINSCOPE(Placement[page_name]), SUM('Placement'[page_revenue]),
ISINSCOPE('Product'[product_name]), SUM('Product'[product_revenue]),
ISINSCOPE('Category'[Category]), SUM('Category'[category_revenue]),
SUM('Total AdSales'[campaign_revenue])
)
RETURN
COALESCE(_values, 0)

Issue is:
The measure returns the correct values when breaking down by city, keyword, placement, product, etc.
However, the total does not reflect the sum of the displayed rows, it seems to follow only the last condition (SUM('Total AdSales'[campaign_revenue])). I can't use separate measures for each table because this measure is used in a field parameter, and restructuring the model is not an option.

What I Need:
A way to ensure the total correctly reflects the sum of the displayed context, without affecting the individual breakdowns. Need a solution that works within my existing setup means without creating separate measures for each table.

Any help or suggestions would be greatly appreciated🙏
Thanks in advance

[johnt75 · Accepted solution]

You could create a disconnected table containing the different types of table you are showing - City, Keyword match type etc. Add this as a filter to each table visual, selecting just the value applicable to that table.

Change your measure so that instead of using ISINSCOPE you check the SELECTEDVALUE of the disconnected table column, and then perform the appropriate calculation.

[vm8181]

Thanks for your response @mark_endicott @johnt75  @weiqb 

Yes, creating a disconnected table is the only viable solution in this case. I implemented this approach using a field parameter, and it’s now working fine.

@weiqb , ISFILTERED, ISINSCOPE, and ISCROSSFILTERED return the correct total only when the context comes from a single table. However, when the context comes from multiple tables, these functions do not work as expected for total calculations.

Thanks for insights🙏

[weiqb]

Differences between isinscope/isfiltered/iscrossfiltered, Try ISFILTERED/ISCROSSFILTERED
Measure:

City Is In Scope = ISINSCOPE('city gdp'[City])

City Is Filtered = ISFILTERED('city gdp'[City])

City Is Crossfiltered = ISCROSSFILTERED('city gdp'[City])

[mark_endicott · Accepted solution]

@vm8181 - You won't be able to acheive this with ISINSCOPE - because that is always FALSE at the total level - without an incredibly horrendous final condition. 

Honestly, the best way to acheive this is by modelling your data - I know you say this is not an option, but someone in your organisation can access the model and help you with it. A disconnected table that unions all your appropriate revenues together and then can be used as filter will make this 100 times easier than trying to solve with DAX alone. 

[johnt75 · Accepted solution]

You could create a disconnected table containing the different types of table you are showing - City, Keyword match type etc. Add this as a filter to each table visual, selecting just the value applicable to that table.

Change your measure so that instead of using ISINSCOPE you check the SELECTEDVALUE of the disconnected table column, and then perform the appropriate calculation.