Sum measures for multiple what if / parameter scenarios

[o59393]

Hi all!

I need some help getting the sum of 5 measures that are subject to change to a country slicer and a parameter that increases or decreases each measure.

What I need the dax measure to do is: calculate the complaints based on:

1) the country or countries selected

2) The value given in the parameter

3) the month(s) selected 

The dax measure that is function of the parameter is:

# complaints adjustment 1 = ROUNDUP(SUM(complaints[number_complaints]) * (1 + '# of Complaints Adjustment 1'[# of Complaints Adjustment 1 Value]),0)

In other words, it takes the number of complaints and multuplies it by the percentage defined in the parameter.

I did this dax for 5 different adjusments scenarios. And looks something like this:

As seen in the image, the

(1) is in function of (a), (x) and the date.

(2) is in function of (b), (y) and the date.

(3) is in function of (c), (z) and the date.

(4) is in function of (d), (w) and the date.

(5) is in function of (e), (wx) and the date.

The measure that sums 1, 2,3,4 and 5 is:

# complaints adjustment all = 
complaints[# complaints adjustment 1] + 
complaints[# complaints adjustment 2] + 
complaints[# complaints adjustment 3] + 
complaints[# complaints adjustment 4] + 
complaints[# complaints adjustment 5]

However that dax( # complaints adjustment all) is not returning me the sum which should based on the image = 338+159+58+144+235 = 934

Can you please tell me what I should do to get right the total based on the what if scenarios?

I attach pbix.

https://www.mediafire.com/file/o4aksw89iwac4yr/QSE_Model_2.pbix/file

Thanks!!

[v-alq-msft · Accepted solution]

Hi, @o59393 

You may create five calculated tables as follows.

Country 1 = DISTINCT('facilities'[country])
Country 2 = DISTINCT('facilities'[country])
Country 3 = DISTINCT('facilities'[country])
Country 4 = DISTINCT('facilities'[country])
Country 5 = DISTINCT('facilities'[country])

Then you can create five measures as below.

Measure 1 = CALCULATE('complaints'[# complaints adjustment 1],facilities[country] in FILTERS('Country 1'[country]))
Measure 2 = CALCULATE('complaints'[# complaints adjustment 2],facilities[country] in FILTERS('Country 2'[country]))
Measure 3 = CALCULATE('complaints'[# complaints adjustment 3],facilities[country] in FILTERS('Country 3'[country]))
Measure 4 = CALCULATE('complaints'[# complaints adjustment 4],facilities[country] in FILTERS('Country 4'[country]))
Measure 5 = CALCULATE('complaints'[# complaints adjustment 5],facilities[country] in FILTERS('Country 5'[country]))

Here is the total measure:

TotalMeasure = [Measure 1]+[Measure 2]+[Measure 3]+[Measure 4]+[Measure 5]

Result:

If I misunderstand your thought, please show me your expected result. I am glad to solve the problem for you.

Best Regards

Allan

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

[o59393]

This is perfect @v-alq-msft  I appreciate a lot your help!!!!

[d_gosbell]

So the issue here is the way you have edited interactions with the country filters. Currently each of your tables numbered 1-5 is only filtered by a single country slicer. While the grand total is filtered by all of them. So effectively it's trying to calculate the total based on a filter of Country = "Costa Rica" AND Country = "Columbia" AND Country = "Dominican Republic" etc. And the combination of these filters eliminates all the rows.

The only work around I can think of is to create 5 "what if" versions of you facilities table, and use these in your country slicers and in your measures for items 1-5. 

[o59393]

Hi @d_gosbell , please see the solution by @v-alq-msft .

Thanks!!

[d_gosbell]

---

@o59393 wrote:

Hi @d_gosbell , please see the solution by @v-alq-msft .

Thanks!!

---

Yes, I saw that. That solution is basically just an implementation of the last sentence in my previous response