topic Pareto Curve in Power BI/DAX - % customers vs % sales in DAX Commands and Tips

Pareto Curve in Power BI/DAX - % customers vs % sales

akhoury — Thu, 06 Aug 2020 11:57:51 GMT

Hello dear community members,

I've been trying to solve this for a while, but not luck so far.

I need to have a 'Sales Concentration Curve', that is a Pareto curve basically, but instead of having CUSTOMERS on the X-Axis, having a percentage (i.e: X% of customers give Y % of volume).

I got this for now:

It only works if I add the Bar values (that is sales value) which is basically Sum of Sales Value. and creating the following measure which draws the curve using the below code:

Cust volume Concentration =

VAR total_SalesVol = CALCULATE(SUM(oos_master_customer[Sales LY]),ALLSELECTED(oos_master_customer))

VAR current_SalesVol = SUM(oos_master_customer[Sales LY])

VAR summarized_Table =

SUMMARIZE(

ALLSELECTED(oos_master_customer),

oos_master_customer[customer_unique_code],

"volume",SUM(oos_master_customer[Sales LY])

)

VAR CumulativeSum =

SUMX(

FILTER(summarized_Table,[volume] >= current_SalesVol),

[volume]

)

RETURN

DIVIDE(CumulativeSum,total_SalesVol)

The curve is redrawn appropriatly when applying filters on the graph. So all works perfectly.

What I am missing: is to have on the X-axis, instead of the customer (11k of them), just percentage 0-100% based on their ranking of largest to smallest.

My model has: 1 Transaction (facts) table for sales

linked to 1 Customer (Dim) table

linked to 1 Product (Dim) table (to the facts table obviously)

Thanks a lot

I spent too many hours trying to figur that out - your help is very much appreciated. thanks a lot

Re: Pareto Curve in Power BI/DAX - % customers vs % sales

akhoury — Sun, 09 Aug 2020 10:24:58 GMT

testing the reply as I saw no takers yet - apologies for the spam

Re: Pareto Curve in Power BI/DAX - % customers vs % sales

mahoneypat — Sun, 09 Aug 2020 13:22:38 GMT

Do you already have a percentile column in your customer table? If not, I would add a calculated column using one of the PERCENTILE functions to show the percentile of each customer. You can then write a measure that shows the running total percent for your Y axis (i.e., include current percentile and all lower/higher percentiles).

If you need it to be dynamic with slicers, you will need a disconnected table with the percentile values for your X axis, and calculate the %Y dynamically. If you post a link to the pbix, more specific help can be given.

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Pareto Curve in Power BI/DAX - % customers vs % sales

akhoury — Mon, 10 Aug 2020 06:31:45 GMT

First Thanks a lot for your reply, Pat.

My objective is to implement the second solution you proposed; I already have a version with your first proposed aproach. The problem is that its hardcoded and it's not dynamic/wouldn't filter.

As to the second aproach, I did read before that it will need a table (or a table within a measure possibly/if I understood well), but I don't think I have the DAX experience level required to be able to implement it. I have been trying for a while.

Here is the pbix link: https://we.tl/t-R1PSSzUOmT

(Tried to reduce its size as much as possible, leaving just what is needed)

In this PBIX you will find the sample (hard coded) curve on the right, just to explain what I need have as an output, and on the left hand side, the concentration curve I already have, but listing all the customers on the x-axis; its missing what I was mentionning, that is to have them as %. 1-100%, 1% corresponding the largest customers and 100% to the smallest.

Thanks again.

Amine

Re: Pareto Curve in Power BI/DAX - % customers vs % sales

akhoury — Wed, 12 Aug 2020 19:28:01 GMT

Testing to see if the thread is still accepting replies