Forum Discussion

Thigs's avatar
Thigs
Icon for Helper IV rankHelper IV
4 years ago
Solved

Pareto without Bars?

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's avatar
    AlexisOlson
    4 years ago

    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.

5 Replies

    • Thigs's avatar
      Thigs
      Icon for Helper IV rankHelper IV
      4 years ago

      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's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        4 years ago

        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.