Percentage ranges in slicer

[jnn4282]

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?

[v-kkf-msft]

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.

[jnn4282]

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.

[v-kkf-msft]

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%])

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.

[jnn4282]

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

[tamerj1]

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

[jnn4282]

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 

[v-kkf-msft]

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

[jnn4282]

Hi Winniz,

Here is the link to my Power bi file.

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

[v-kkf-msft]

Hi @jnn4282 ,

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

Best Regards,
Winniz

[jnn4282]

You are my hero.

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

[jnn4282 · Accepted solution]

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.

[jnn4282]

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.

[jnn4282]

@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.

[amitchandak]

@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

<use those >

What if

[jnn4282]

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?