Pareto without Bars?

[Thigs]

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!

[AlexisOlson]

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

[Thigs]

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?

[AlexisOlson · Accepted solution]

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.

[Thigs]

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. 

[Anonymous]

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.