Monthly distinct count for top 90% suppliers based on 12 month rolling sum of purchase values.

[timr123]

Hi,

I'm not sure if I've aproaching this in the best way and I've got to a point and I'm stuck. I'm actually hoping there's a more simple solution.

What I'm trying to do is get a monthly distinct count of the top 90% of suppliers based on the last 12 months of purchases.

Every month look back at the last 12 months worth of purchases and count the number of suppliers that made up the top 90% of the purchase value ($).

This is what I have so far.

A measure that works out the 12 month rolling sum for a supplier and a measure to get the 12 month total.

From these two I get the percentage.

My next idea is to rank the percentage and then make a cumulative measure adding up the percentages ordered by the rank.

From there I could filter out any suppliers that have a cumilative percentage of over 90.

The last part I'm not sure if it can work and also where I'm stuck.

Hopefully this makes sence 

1SUMXEUR2 = SUMX(Query1,Query1[EUR2])

1Supplier12Mrolling = CALCULATE (
    [1SUMXEUR2],
    DATESBETWEEN (
        'Date'[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) ),
        LASTDATE ( 'Date'[Date] )
    )
)

1Total12monthrolling = CALCULATE (
    [1SUMXEUR2],ALLSELECTED(Query1),
    DATESBETWEEN (
        'Date'[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) ),
        LASTDATE ( 'Date'[Date] )
    )
)

1PercentagePurchases = [1Supplier12Mrolling] / [1Total12monthrolling]

1Rank = RANKX( ALLSELECTED(Query1) , 'Query1'[1Supplier12Mrolling] )

The rank is not working, the rank for a month is in order but there are ties over learge value range that there shouldn't be.

1CumulativePercentage= 
SUMX (
    FILTER (
        ALLSELECTED ( Query1 ),
       [1Rank] <= MAX ( [1Rank] )
    ),
    [1PercentagePurchases]
)

And then when it gets to cumulitive percentage I cannot use the measure in the MAX which also has me stuck.

And I'm not even sure if I manage to get the cumulitive measure for the percentage I could use that as a filter in a graph?

Any guidance or help would be apprecieted. 

Cheers

Tim

[v-jiascu-msft]

Hi Tim,

Firstly, I would suggest you provide a dummy sample file. The measures here look good. But they could give you useless results. We need a sample to test. 

Secondly, please download the demo in the attachment. I hope it can give you some ideas.

Approach 1. Create some measures. 

12 =
VAR currentRank = [12Rank]
RETURN
    CALCULATE (
        SUMX (
            FILTER (
                ADDCOLUMNS (
                    DISTINCT (
                        CROSSJOIN (
                            SELECTCOLUMNS ( DimProduct, "cn", [ColorName] ),
                            SELECTCOLUMNS (
                                DimDate,
                                "Year", DimDate[Datekey].[Year],
                                "Month", DimDate[Datekey].[Month]
                            )
                        )
                    ),
                    "percentage", [12%],
                    "ranks", CALCULATE ( [12Rank] )
                ),
                [percentage] > 0
                    && [ranks] <= currentRank
            ),
            [percentage]
        ),
        ALL ( DimProduct[ColorName] )
    )

Approach 2: create a calculated table.

Best Regards,
Dale

[timr123]

Hi @v-jiascu-msft,

That looks like it will achieve what I'm after. I'll let you know how I get on tomorrow.

Thanks!

[timr123]

@v-jiascu-msftthanks for the sample file, that worked a perfectly!!

The last piece is the top 90% supplier count. Unfortunatly when I add this measure to a visual it's very very slow to calculate and also often times out due to memory. Any idea how to make it more efficent?

90% > BP rolling count = CALCULATE(
	DISTINCTCOUNT(Query1[t_bpid]),
    FILTER ( 'Query1', [12] < 0.9 )
    ,
        DATESBETWEEN('Date'[Date],
	   STARTOFMONTH(DATEADD(ENDOFMONTH('Date'[Date]), -12, MONTH)),
	   ENDOFMONTH('Date'[Date]))
        )

Thanks

Tim

[v-jiascu-msft]

Hi Tim,

Which approach did you choose? I actually left the last step undone in the demo. The performance of the first approach will be very bad if we calculate the numbers of less than 90%. Because we have to nest [12] to another table. 

Regarding your formula here, the DATESBETWEEN seems unnecessary. Please give it a try.

Best Regards,
Dale

[timr123]

Hi @v-jiascu-msft,

I used the calculated table. I removed DATESBETWEEN and it still consumes all the avalible memory until it fails.

Thanks

Tim

[v-jiascu-msft · Accepted solution]

Hi Tim,

Good choice. That could make the report faster. Please download the demo again. 

1. Add a column and establish a relationship. It could be necessary for the production environment. 

2. All the calculations only relate to the Query 1 now.

3. Create a new measure. 

percentageRunningTotal =
CALCULATE (
    SUM ( Query1[percentage] ),
    FILTER (
        ALL ( Query1[ranks], Query1[ColorName] ),
        Query1[ranks] <= MIN ( Query1[ranks] )
    )
)

4. Create a new final measure. You can modify it according to your model.

90% > BP rolling count =
CALCULATE (
    COUNTROWS ( 'Query1' ),
    FILTER (
        ALL ( Query1[ColorName], Query1[ranks] ),
        [percentageRunningTotal] <= 0.9
    )
)

Best Regards,
Dale