Count how many clients within the best 80% turnover

[hgalfre]

Hi there! 

So I've been working on this for quite a while, looked at all sorts of forums and articles about a same issue, but I couldn't find something that solved mine.

I would like, in my PBI report, to show the distinct count of the best clients that make 80% of our sales. 

The table I use for that presents itself as a sales table but that creates a line everytime there's a modification in the order. 

It runs from 2017 till today, and the date columns is actually a month column. 

At first, I was just trying to create measures to that table, but it is quite heavy, and as soon as I added filters or cumulative turnover, it would crash and tell me I hadn't sufficient memory. 

My table looks like this :

Client ID	Turnover	Order ID	Date
101	1000	1	07/2020
101	2000	1	07/2020
102	300	2	08/2020
102	5002	3	01/2021
Z103	1000	4	02/2021

The Date column has an active relationship to a Date table.

The client ID has an active relationship with the Client table that has the name of the client inside. 

What I would like is to have this:

Client Name (from another table)	Total turnover (selected year in a filter)	Client turnover rank	Cumulative turnover	% of total turnover of the year
ABC	5 000 000	1	5 000 000	42%
BRC	4 600 000	2	9 000 000	38%
UYT	2 400 000	3	12 000 000	20%
Total	12 000 000	3	12 000 000

And a visual that says: 

80% of the turnover of 2021

2 clients

> What I've tried so far:

1st trial:

>A measure to automatically calculate the rank by turnover:

Rank =

IF (

HASONEVALUE ( 'Table'[Client ID]),

RANKX (

ALLSELECTED ( 'Table'[Client ID] ),

CALCULATE ( SUM ( 'Table'[Turnover] ) ),

,

DESC,

DENSE

)

)

>Then a measure that uses the rank to calculate a cumulative turnover:

Cumulative turnover =

VAR CurrentRANK = [Rank]

RETURN

SUMX (

FILTER (

ALLSELECTED ( 'Table'[Client ID]),

CALCULATE ( [Rank]) <= CurrentRANK

),

CALCULATE ( SUM ( 'Table'[Turnover]) )

)

>Then a measure to calculate de % of the cumulative turnover on the total turnover

%cumulative turnover =

[Cumulative turnover]/ CALCULATE (SUM('Table'[Turnover]), ALLSELECTED ('Table'[Client ID]))

I thought that after that I would be able to find a quick measure to count all the client ID that have a cumulative turnover below 80%.

The visual outcome is good, like this:

Client ID	Rank	Cumulative turnover	%cumulative turnover
ABC	1	5 000 000	42%
BRC	2	9 000 000	38%
UYT	3	12 000 000	20%

It seems to be working if I add the Years in a filter for the visual.

However, I would like to display the name of the client, so if I pull it from the client table, I get an error message after 5-10min saying there's not enough memory to do it.

2nd trial:

So, seeing that it was too slow, I've tried another solution; summarizing my table and then doing the exact same work. 

>First, my code for the new table:

Test table = SUMMARIZE('Table','Table'[Client ID]], 'Client table'[Name],'Table'[Date],"Turnover", SUM('Table'[Turnover]))

> Then my code is the same as previously. The table works fine... until I try to add the client name. Memory issue again.

I guess that's logical since the source remains the same table, but I had to give it a try.

I've seen a solution from SQLBI using tabular editor and group calculation, but unfortunately, it's not yet planned to ask an extra budget to my IT team.

Does anyone know any way I could reduce this piece of code so that it could be faster and avoid the memory issue?

Thanks for your help!

Happy new year or happy end of year to you all! 

[hgalfre]

Hi @mahoneypat !

So, I've tried your measure with a sample pbi (not as big as mine ; I have 24 tables, the Turnover one has more than 1 million lines...). 

The measure seemed to be working for the full table, my third client, cumulated with the first two, makes 80% of the turnover, so the measure counts 3 clients. 

However, I tested it also with a year filter; hence wanted to know the count of clients with 80% turnover only for year 2021. 

It gives me 2 clients with your measure, although the first 2 only make 70% of the turnover. Which is understandable, as the measure says "<=0.8". 

However, if I change it to ">=0.8" ; I end up with 4 clients counted, which works as well. 

Here's the recap of the 2021 turnover from the sample data:

Client ID	Turnover	Cumulative	% cumulative
504	34754	34754	46%
102	17902	52656	70%
101	13702	66358	88%
505	6002	72360	96%
104	2000	74360	99%
Z103	1000	75360	100%

In my personal pbi, I get this when extracting the turnover for all client IDS for 2021. 

The visual card with your measure says 124 clients, but as you can see, the 124th is 85% of the turnover.

Client ID	%cumulative turnover	ranking
7000651	79,9%	108
9403059	80,3%	109
8052847	80,6%	110
4030698	81%	111
8054507	81%	112
5026798	82%	113
8000563	82%	114
7046200	82%	115
4058709	83%	116
5160809	83%	117
7700144	83%	118
3173225	83%	119
4043551	84%	120
5171330	84%	121
7000654	84%	122
8094761	85%	123
8028510	85%	124

Could it be that the measure is misreading the year filter?

In my personal PBI, I have a calendar table, so I just added "year" to a filter to the visual. 

In the sample data, It automatically made a date hierarchy from the date. No extra table.

Here is a link to my sample data:

https://we.tl/t-vP6wSHlZpM 

Thanks for your help!

[mahoneypat]

Thought about it some more. Here is a pattern that should work for a measure expression in a card. Results shown for your sample data. One failure mode of that approach is if there is a big gap in turnover between your last one included and first one excluded. You can add a ROUNDOWN([cTurnover],-1) if needed to make it more robust in the final FILTER expression. That returns 3 instead of 1 in the pic below.

Note that you can just add your Client Name into the Summarize if needed.

SUMMARIZE(Turnover, Turnover[Client ID], Client[Client Name]). 

Clients Top 80 =
VAR turnovertable =
    SUMMARIZE (
        Turnover,
        Turnover[Client ID],
        "cTurnover", SUM ( Turnover[Turnover] )
    )
VAR totalturnover =
    SUMX ( turnovertable, [cTurnover] )
VAR cumulativeturnover =
    ADDCOLUMNS (
        turnovertable,
        "cCumulTurn",
            VAR thistotal = [cTurnover]
            RETURN
                DIVIDE (
                    SUMX ( FILTER ( turnovertable, [cTurnover] >= thistotal ), [cTurnover] ),
                    totalturnover
                )
    )
VAR result =
    COUNTROWS ( FILTER ( cumulativeturnover, [cCumulTurn] <= 0.80 ) )
RETURN
    result

Pat

[mahoneypat]

@hgalfre Did this approach work for you?  If so, please accept it as the solution.

Pat

[hgalfre]

Hi Pat! 

Sorry for my late answer, I had some emergencies at work. 

I'll be looking at your solution asap. 

Do you stilll need a sample of my pbi (it would take some time though, but I can provide it if necessary)?

Thanks!

[mahoneypat]

Sample file not needed, if that expression works out for you.

Pat

[hgalfre]

Hi there! 

Sorry for taking so much time to answer.. I've just tried your solution, it seems to be working; I get 124 customers for 2022 for the top 80% CA (in the kpi card), but when I extracted the data, to compare it, I found 109 customers. I can try to send you a proper sample file if needed

[mahoneypat]

FYI that Tabular Editor 2 is free, but I don't think a calc group is necessarily going to speed things up. When helping to optimize DAX, it is helpful to have a similarly sized dataset. Can you provide a link to your pbix? or to one with a mock set of representative data of the same size?

Pat