topic Pareto without Bars? in DAX Commands and Tips

Pareto without Bars?

Thigs — Wed, 05 Jan 2022 19:00:24 GMT

Hi all

I'm trying to make a Pareto chart but I'm having an issue. Basically, it works, but my x-axis (skus) is huge (several thousand). So what I want is to make the x-axis % of skus, but obviously, I can't put a measure as the x-axis.

Essentially, I am trying to answer the question, what % of my skus are in my top 5,10,20, etc % of sales and show it visually.

Here is what I'm using for the Pareto line, but it won't really work without the bars -

Pareto =

VAR TotalVolume = CALCULATE(SUM('Daily Fact Table'[Quantity]),ALLSELECTED('Daily Fact Table'))

VAR CurrentVolume = SUM('Daily Fact Table'[Quantity])

VAR SummarizedTable =

SUMMARIZE(

ALLSELECTED('Daily Fact Table'),

'Daily Fact Table'[SKU],

"Volume", SUM('Daily Fact Table'[Quantity])

)

VAR CumulativeVolume =

SUMX(

FILTER(SummarizedTable, [Volume] >= CurrentVolume),

[Volume])

RETURN

CumulativeVolume/TotalVolume

My x-axis is SKUs and my Y-Axis is quantity

Any help is greatly appreciated!

Re: Pareto without Bars?

AlexisOlson — Wed, 05 Jan 2022 21:28:23 GMT

You'll need to define a new dimension table to use on your x-axis and then write your measures to read this value as the percent to use.

Axis = GENERATESERIES ( 0, 1, 0.01 )

Here's a simple example showing how to use a parameter table in a measure:

https://stackoverflow.com/questions/56956190/how-to-use-a-measure-as-an-axis-in-power-bi

Re: Pareto without Bars?

Thigs — Thu, 06 Jan 2022 14:09:28 GMT

I'm still a little confused. I made the Axis table, but I'm confused where to change the axis in my formula. Since my SKUs are not currently done by percent, will I need to make a new column where they are done by percent?

Re: Pareto without Bars?

AlexisOlson — Thu, 06 Jan 2022 16:05:23 GMT

It is indeed relatively complex, so I will provide an actual working example.

I downloaded the .pbix from this article and created a new Axis table and a new measure:

Bucket Sales = VAR BucketMin = SELECTEDVALUE ( 'Axis'[Value] ) VAR BucketMax = CALCULATE ( MIN ( 'Axis'[Value] ), 'Axis'[Value] > BucketMin ) VAR NonZeroProducts = FILTER ( VALUES ( Products[ProductKey] ), [Total Sales] > 0 ) VAR NonZeroCount = COUNTROWS ( NonZeroProducts ) VAR Summary = ADDCOLUMNS ( NonZeroProducts, "@Rank%", DIVIDE ( RANKX ( NonZeroProducts, [Total Sales] ), NonZeroCount ) ) VAR BucketSum = SUMX ( FILTER ( Summary, [@Rank%] >= BucketMin && [@Rank%] < BucketMax ), [Total Sales] ) RETURN DIVIDE ( BucketSum, NonZeroCount )

The result looks like this:

See the attached modified file.

Re: Pareto without Bars?

Thigs — Thu, 06 Jan 2022 19:42:21 GMT

This is very helpful, thank you!

I am clearly messing something up. I replaced the values in the formula with my own, and I'm getting the following graph.

Re: Pareto without Bars?

Anonymous — Tue, 11 Jan 2022 06:07:32 GMT

Hi Thigs ,

I think AlexisOlson 's reply is helpful and it should be the workaround to your problem. If you still couldn't solve your problem by this reply, please share a sample file with us. This will make it easier for us to check your formula and find the solution.

Best Regards,
Rico Zhou

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