Assigning Percentile Range based off Product/Retailor by Spend

[abraun]

Hi All,

I'm dealing with quite a puzzle at work within Pbi that I/ChatGPT/Co-pilot can't figure out.

I created a summary table of this dummy data set to showcase what I'm trying to do:

Retailor	Product	Revenue
Walmart	iPhone	$ 776,386.00
Target	iPhone	$ 748,005.00
BestBuy	iPhone	$ 748,885.00
Apple	iPhone	$ 436,194.00
Staples	iPhone	$ 896,851.00
ATT	iPhone	$ 241,100.00
Verizon	iPhone	$ 556,348.00
Walmart	iPad	$ 699,152.00
Target	iPad	$ 675,205.00
BestBuy	iPad	$ 520,993.00
Apple	iPad	$ 831,657.00
Staples	iPad	$ 513,828.00
ATT	iPad	$ 903,435.00
Verizon	iPad	$ 339,119.00

I'm trying to create a table visual in PowerBI that has a column added called "Percentile Range" which assigns a Percentile range of sales based off the product by retailor.

For example, since Staples had the highest revenue for Iphone sales, the value in this added column would be "90th Percentile". While ATT (who had the lowest sales) would output a value that says "10th Percentile".

I need to do this for each individual product types (aka Ipad sales would have it's own percentile range because it's different from Iphone)

If anyone can help me figure out how to isolate revenue to specific product I would greatly appreciate it thanks!

Here's what I tried so far, but this formula assigns the percentile range based off the total revenue of all products.

Percentile Range Measure = 
VAR TotalRev = SUM(DummyData[Revenue]) //Sums Revenue of OG dummy data that has spend of individual transactions

//Percentile range based off summary table and column "TotalRev" that is SUM(DummyData[Revenue])

VAR Percentile90 = PERCENTILEX.INC(RevSummaryTable, [TotalRev], 0.9)
VAR Percentile75 = PERCENTILEX.INC(RevSummaryTable, [TotalRev], 0.75)
VAR Percentile50 = PERCENTILEX.INC(RevSummaryTable, [TotalRev], 0.5)
VAR Percentile10 = PERCENTILEX.INC(RevSummaryTable, [TotalRev], 0.1)


RETURN
    IF(TotalRev >= Percentile90, "90th Percentile",
    IF(TotalRev >= Percentile75, "75th Percentile",
    IF(TotalRev >= Percentile50, "Middle Range",
    IF(TotalRev >= Percentile10, "10th Percentile", "Bottom Percentile")))

[Anonymous · Accepted solution]

Hi,@abraun 
I am glad to help you.

Since you didn't give data for your other tables therefore I created additional test data:

I have created a new table using dax code that will aggregate the Revenue based on the Retailor, if a single Product has multiple Retailors with the same name (e.g. iPad products with two or more ATT's)
The table aggregates the Revenue of multiple Retailers with the same name to calculate the total value.

RevSummaryTable = 
SUMMARIZE('DummyData','DummyData'[Retailor],'DummyData'[Product],"TotalRev",
    VAR _current_product = 'DummyData'[Product]
    RETURN CALCULATE(SUM(DummyData[Revenue]),FILTER('DummyData','DummyData'[Product]=_current_product))
  )

Here is the result:

If you could provide more non-sensitive information (in the form of a .pbix file), it would be helpful in solving your problem!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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