Calculating same measure with different filter context

[preetjawaria]

Sample Data:

Acct Name	Bill Name	Report Count
A1	Null	7
A2	Null	12
Null	B1	13
Null	B2	9
Null	B3	3

I have a scenario now - I created 2 slicer - Acct Name & Bill Name

I am presenting the SUM(Report Count) in two different Card Visuals-

1. where in one place the measure/Card visual affected by only Acct Name Slicer (configured By Edit Interactions)

2. In another place the measure/Card Visual gets affected by only Bill Name Slicer. (configured By Edit Interactions)

Requirement:

I want to have a calculation (Total Measure) which will return me the SUM of (Value appearing in point 1 , Value appearing in Point 2) , so whatever value I select from both the slicers accordingly the Total Measure should change.

At last I want to show the Total Measure in a Card Visual only.

[v-jianboli-msft]

Hi @preetjawaria ,

Approve with @TomMartens , If you want to achieve this, you may need to create a new table.

Please try:

First create a new table for slicer:

Then use the two columns to create two slicers

Apply the measures:

Measure For Acct Name = CALCULATE(SUM('Table'[Report Count]),FILTER('Table',[Acct Name]=SELECTEDVALUE('For slicer'[Acct Name])))

Measure For Bill Name = CALCULATE(SUM('Table'[Report Count]),FILTER('Table',[Bill Name]=SELECTEDVALUE('For slicer'[Bill Name])))

Total = [Measure For Acct Name]+[Measure For Bill Name]

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[preetjawaria]

Thanks for the inputs. everyone.

[preetjawaria]

Hi @TomMartens , you understood the scenario correctly, now lets say you select value A1 from first slicer and B2 from the other slicer - then the card visuals will show 7 and 9 respectively.

So the Total Measure should return 16 (7+9) to us.

Am I clear now ?

[TomMartens · Accepted solution]

Hey @preetjawaria ,

your requirement can not be solved having only a single table, this due to the concept of Auto-Exist: Understanding DAX Auto-Exist - SQLBI

My recommendation is to create a proper star schema with separate Dimension Tables Account and Bill, you might also consider creating two fact tables one for BILL and one for ACCOUNT.

Regards,
Tom

[TomMartens]

Hey @preetjawaria ,

I'm not 100% clear about the expected result.
From my understanding there are currently two card viusals that are only affected by their corresponding slicers (configured by edit interaction).
My assumption is that these two slicers do not affect each other as well. Is this assumption correct.

What is the expected result for the Total Measure if nothing is selected from both card visuals, meaning both card visuals are showing the value 44 as in the in the screenshot below:

Can you please describe why you can't use a simple measure like the one below for calculating the measure Total Measure:

Total Measure = SUM( 'your table name'[report count] )

Regards,
Tom