topic Re: Percentage ranges in slicer in DAX Commands and Tips

Percentage ranges in slicer

jnn4282 — Sun, 27 Feb 2022 09:49:00 GMT

I am working with the project to control the cost and improve the revenue.
For example, Total sales for this FY were 1M, Labor was 25%, COGS was 28.5%, and so on.
My concern is, " I will create a slicer with the range of 0-0.5%,0.5-1%,1-1.5% up to 5%.
If I select a 1-1.5% value, the Labour and COGS should be 24-23.5% and 27.5-27%, respectively.

Would anybody guide me, please?

Re: Percentage ranges in slicer

amitchandak — Mon, 28 Feb 2022 06:14:44 GMT

jnn4282 , You should be able to use what is slicer

You can two value from range in a measure =

measure =

var _min = minx(allselected(whatif), whatif[value])

var _max = maxx(allselected(whatif), whatif[value])

return

What if

Re: Percentage ranges in slicer

jnn4282 — Mon, 28 Feb 2022 07:31:47 GMT

Dear amitchandak ,

Thank you so much for your prompt response.

I am new to Power Bi and not able to sort out your description.
Creating a measure with min and max var and after the function allselected , I dont know what next?

Re: Percentage ranges in slicer

v-kkf-msft — Wed, 02 Mar 2022 01:39:35 GMT

Hi jnn4282 ,

Not sure about your data structure, I created the following example data.

Then we need to create a new table for the slicer.

Slicer = ADDCOLUMNS ( GENERATESERIES ( 0, 4.5, 0.5 ), "RangeEnd", [Value] + 0.5, "Range", [Value] & "-" & [Value] + 0.5 & "%" )

Then create meatures to calculate the minimum and maximum values respectively.

Minrate = SUM( 'Table'[rate] ) - SELECTEDVALUE ( Slicer[RangeEnd] ) / 100

Maxrate = SUM ( 'Table'[rate] ) - SELECTEDVALUE ( Slicer[RangeStart] ) / 100

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Percentage ranges in slicer

jnn4282 — Wed, 02 Mar 2022 03:43:26 GMT

This is what I am working on.
I created a slicer measure as per your instruction. Tabel with category and rate doesn't need to create, since I am pulling data from P&L. My concern is by selecting slicer created, the profit may change. By decreasing the COGS and Labor by 0.5 % how much the impact has on Profit.
I hope my question is clear.
Thanks in advance for supporting me.

Re: Percentage ranges in slicer

v-kkf-msft — Wed, 02 Mar 2022 09:28:53 GMT

Hi jnn4282 ,

Do you mean that COGS%, Profit, Labor% are columns in the table? If so, are these columns summed in these cards?

I see that Profit + COGS% + Labor% <> 100%, so how is Profit calculated?

I assume that these values are summed and Profit = 1 - COGS% - Labor% - Other%, then the following measures can be created to show the filtered results.

Max_COGS% = SUM('P&L'[COGS%]) - SELECTEDVALUE(Slicer[RangeStart])/100 Min_COGS% = SUM('P&L'[COGS%]) - SELECTEDVALUE(Slicer[RangeEnd]) / 100 Max_Labor% = SUM('P&L'[Labor%]) - SELECTEDVALUE(Slicer[RangeStart]) / 100 Min_Labor% = SUM('P&L'[Labor%]) - SELECTEDVALUE(Slicer[RangeEnd]) / 100 Max_Profit = 1 - [Min_COGS%] - [Min_Labor%] - SUM('P&L'[Other%]) Min_Profit = 1 - [Max_COGS%] - [Max_Labor%] - SUM('P&L'[Other%])

Re: Percentage ranges in slicer

jnn4282 — Wed, 02 Mar 2022 14:54:19 GMT

P&L

I don't know its proper way to share the data or not. My data is as attached.
I did understand your response a lot. My data structure is a bit complicated. The subject column is the main focus point. With this data, I want to show how the number changes if we reduce COGS and Labour by a certain percentage.

Re: Percentage ranges in slicer

jnn4282 — Wed, 02 Mar 2022 15:00:35 GMT

v-kkf-msft
By the way, thank you so much for your time and sincerity. I really appreciated your time and dedication. I am quite new to power bi.

Re: Percentage ranges in slicer

jnn4282 — Sat, 05 Mar 2022 09:49:59 GMT

Did you get a chance to look at my data, which I attached as link P&L?

Re: Percentage ranges in slicer

tamerj1 — Sun, 06 Mar 2022 16:50:51 GMT

jnn4282
The link you've provided is not working, You can upload to WeTransfer for example and share the link within your reply.

Re: Percentage ranges in slicer

jnn4282 — Sun, 06 Mar 2022 23:45:56 GMT

Dear tamerj1
Thank you for your response.

I have sent a link below so that you can check my data.

https://we.tl/t-1omTtL3XkJ

Please help me

Re: Percentage ranges in slicer

v-kkf-msft — Mon, 07 Mar 2022 09:07:48 GMT

Hi jnn4282 ,

I still don't understand how you want to calculate the Profit value after the range change. For example, I choose 1-1.5% and then calculate the maximum and minimum values for COGS% and Labor%.

How do you then want to calculate the Profit value? As the formula shows, I am not sure what the TotalResult value is. If it is still 203663214, then you only need to add 2% to the original Profit. If not, what should it be?

Could you please give a detailed example?

Best Regards,
Winniz

Re: Percentage ranges in slicer

jnn4282 — Mon, 07 Mar 2022 09:44:33 GMT

Thank you so much for your valuable time.
I want my dashboard with a card for Sales, Profit, COGS, and Labor with 0%(the original).
If I can control COGS and Labor by 0-0.5% or 1-1.5% the Profit number should increase and LABOR and COGS should decrease.

Hope this explanation makes clear .
thank you in advance.

Re: Percentage ranges in slicer

jnn4282 — Mon, 07 Mar 2022 13:08:35 GMT

Hi Winniz,

Here is the link to my Power bi file.

https://we.tl/t-5GEUQf4Kgd

Re: Percentage ranges in slicer

v-kkf-msft — Wed, 09 Mar 2022 08:24:29 GMT

Hi jnn4282 ,

If we select 0.5-1%, is this the output you expect?

Best Regards,
Winniz

Re: Percentage ranges in slicer

jnn4282 — Wed, 09 Mar 2022 08:42:17 GMT

You are my hero.

This is what I want. Thanks a lot and I will definitely remember you if I need more support.