How to make my measure responsive along with variations in other measure

[gianmarco]

Dear All,

I have the following table:

SECTION	NAME	VALUES	DISCOUNT%
Amiga500	It Came From The Desert	50	50
Amiga500	Jaguar XJ220	25	20
Amiga500	It Came From The Desert	100	50
Amiga500	Jaguar XJ220	40	20
Amiga500	Turrican II	200	30
Amiga500	Turrican II	500	30
Amiga500	Toki	500	35
Amiga500	JaguarXJ220	150	20
Amiga500	The First Samurai	200	60

And the following measures:

Measure1-Sum of values = SUM(Table[VALUES]

Measure2-Discounted values = SUMX(Table, IF(Table[SECTION]="Amiga500", [Measure1-Sum of values] * Table[DISCOUNT%]/100, BLANK()))

I created the following visualization in the form of a table:

Name	Values	Measure2-Discounted values
It Came From The Desert	150	75
Jaguar XJ220	215	43
Turrican II	700	210
Toki	500	175
The First Samurai	200	120

             Grand Total            1.765    623

At this point I wanted to make simulations so I set a PARAMETER: Through a slicer I can pick a single name and change its value (Values' grand total is changing too).

I have made this happen by creating other two measures:

Measure3-Values simulation 1 = IF(SELECTEDVALUE('Slicer Name'[Slicer Name])=MAX(Table[Name]),[Measure1-Sum of values]+(1+Parameter[Parameter Value]),[Measure1-Sum of values])

Measure4-Values simulation 2 = IF(HASONEVALUE(Table[Name]),[Measure3-Values simulation 1],SUMX(ALL(Table[Name],[Measure3-Values simulation 1]))

At this point I changed my visualization to appear as the following table:

Name	Measure4-Values simulation 2	Measure2-Discounted values
It Came From The Desert	150	75
Jaguar XJ220	215	43
Turrican II	700	210
Toki	500	175
The First Samurai	200	120

                      Grand Total  1.765                                            623

Now I can change my values through the slicer... BUT...

my Measure2-Discounted values remains stuck.

How should I modify Measure2-Discounted values to make it be responsive along with the changes in values of Measure4-Values simulation 2 made with the use of the parameter?

You guys helped me a lot, I hope you can give assistance to me again with this.

Thank You in advance.

[v-easonf-msft]

Hi,  @gianmarco 

Try to add a measure as below:

Measure5-Discounted values = SUMX('Table', IF('Table'[SECTION]="Amiga500", [Measure4-Values simulation 2] * 'Table'[DISCOUNT%]/100, BLANK()))

If it doesn't meet you requirement,please share more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gianmarco]

Dear @v-easonf-msft , I think we're almost there.

Please, check my Sample  file. 

The problem with your solution is that Measure5 displays an incorrect discounted value.

For example:

- the discounted value of "Turrican II" is doubled because it occurs two times in the database (If I add 100 to my simulated value, my Measure5 reports a discounted value of 60%, instead of 30%).

- the discounted value of "Jaguar XJ220" is tripled because it occurs three times in the database (If I add 100 to my simulated value, my Measure5 reports a discounted value of 60%, instead of 20%).

Thanks for your help

gianmarco

[v-easonf-msft]

Hi, @gianmarco 

Your description confuses me even more.

If it is the situation in the picture below, can you share your specific expected result in excel?

I am also confused about the function of measure3. Can you explain it in detail?

If you want to get the value of times,maybe  you can try formula as below:

Times2 = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[NAME]))

or 

Times = 
var a=CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[NAME]=SELECTEDVALUE(Slicer[Slicer Name])))
return IF(ISBLANK(a),1,a)

Best Regards,
Community Support Team _ Eason

[gianmarco]

Dear @v-easonf-msft , I checked again and your solution is appropriate. Thank you very much.

Anyway, I was trying not to multiplicate my Measure1-sum of values for the value of the parameter, but to sum it/subtract it. That is why my Measure3 indicates the PLUS sign.

I can't explain why it doesn't work this way... Do you have any idea?

Citing your example: If "Turrican 2" would become 800 (original value = 700 + Parameter = 100), the discounted value would become 240 (discounted value associated to "Turrican II" = 30%).

Thanks again

(Gonna accept your solution anyway)

[v-easonf-msft · Accepted solution]

Hi, @gianmarco 

Sorry, I am not very clear about the logic of PLUS sign in your measure3 and cannot explain why it doesn't work this way.
I only know that no matter how the parameters change, the discount rate will never be affected, so I directly use the method of multiplying the discount rate to get the discount value.

If this is your expected result, please check my revised measure.

average discount% = CALCULATE(AVERAGE('Table'[DISCOUNT%]),ALLEXCEPT('Table','Table'[NAME]))

Measure5-Discounted values = [Measure4-Values Simulation2]*[average discount%]/100

(Here I use Measure 6 to correct the wrong total)

Measure6 = SUMX(VALUES('Table'[NAME]),[Measure5-Discounted values]) 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gianmarco]

Dear @v-easonf-msft, thank you for priceless help.

[Anonymous]

You've got to be more precise about what you want your measures to do in response to the slicer. Especially about the first measure you mention.