Grouping by % and show Price Range

[Gerald23]

Hi Everyone,

A few weeks ago I asked a question to get grouping by % working in this thread: https://community.powerbi.com/t5/Desktop/Grouping-prices-in/m-p/2335762#M842947

Now I have another question regarding this report. It currently looks like this:

And right now it shows the count of shops that sell a product for a certain price grouped by % of the Recommened Article Price.

Now I have another end user which would like to see the price ranges that belong with these Percentage groupings. Anyone has any idea how I could achieve this? Attached you find the link to download a sample if needed.

Sample data: https://we.tl/t-FH5e0gyu19

[Gerald23 · Accepted solution]

I managed to get the result I wanted in a different way. Instead of measures I added 2 calculated columns and made some changes in the visuals

1 to calculate the percentage

Percentage = DIVIDE('OnlinePrices'[Online Price],'OnlinePrices'[RRP])

2 to create percentage groups

Percentage Groups = switch(True(),
[Percentage] <= 0.5, "0-50",
[Percentage] > 0.5 && [Percentage] <0.6, "051-60",
[Percentage] > 0.6 && [Percentage] <0.7, "061-70",
[Percentage] > 0.7 && [Percentage] <0.8, "071-80",
[Percentage] > 0.8 && [Percentage] <0.9, "081-90",
[Percentage] > 0.9 && [Percentage] <1.0, "091-100",
[Percentage] > 1.0 && [Percentage] <1.1, "101-110",
[Percentage] > 1.1 && [Percentage] <1.2, "111-120",
[Percentage] > 1.2 && [Percentage] <1.3, "121-130",
[Percentage] > 1.3 && [Percentage] <1.4, "131-140",
[Percentage] > 1.4 && [Percentage] <1.5, "141-150",
[Percentage] > 1.5, "150+"
)

With these I was able to build my visuals a different way so I can see count of shops in a percentage price range and also see the prices when I drill through.

[Gerald23]

Hi,

Thanks for your reaction. But something seems to go wrong now. The price range is no longer dynamic depending on the article number selected. It always checks for the lowest and the highest number in the entire OnlinePrices table. I would like this to be dynamic depending on the article number selected in the slicer.

Also lets say I select article 895432 I see the following: 

But this doesn't seem to correspond to the data in the Online Prices Table

[v-chenwuz-msft]

Hi @Gerald23 ,

range of grouping price.

Please try the following code to create a new column in For X-axis:

Range = 
var _min = FORMAT(PERCENTILEX.INC('OnlinePrices',[Online Price],value(left([Value],FIND("-",[Value])-1))/100),"#.00")
var _max = FORMAT(PERCENTILEX.INC('OnlinePrices',[Online Price],value(left([Value],FIND("-",[Value])-1))/100+0.1),"#.00")
return
_min &"-"&_max

and you will get this. 

create a new column named % and range if you need some x-axis like the following screenshot.

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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