% Grand total for Top N

[Andrey28]

Hello,

I know that there might be already an answer to my question, but I struggle to find an easy solution.

I have a data set with thousands of lines for which I want to do 2 tables. First is backlog by Customer and Service Type, second with Top N expenses by Category. What I struggle to calculate is % of grand total for these Top N expenses vs total backlog. Power BI can easily calculate % of grand total in a table for Top N expenses, but I need % grand total vs whole backlog and need these values to change if I change N in Top N. I assume there shall be some kind of DAX logic. When I add a formula for backlog = sum (expense by category) and divide spend in each category by this DAX expression, it obviously returns me 1 in each case.

See example below. Pretty easy to do in Excel....

Thanks in advance.

Andrey 

[stevedep]

Hi, 

This is doable, can you share the data in table format?

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

Will prep a file for you.

Kind regards,

Steve. 

[Andrey28]

Sure. Here is an example I was referring to. I added additional customer "C". So I am looking at Top 3 by expense Category in this case - % of grand total for all expenses (backlog). Let me know if you need data in another format.

Thanks in advance.

Customer	Category	Year	Type	Cost
A	Tables	2020	Planned Spend	20
A	Chairs	2020	Planned Spend	30
A	Forks	2020	Planned Spend	40
A	Knives	2020	Planned Spend	10
A	Napkins	2020	Planned Spend	20
A	Cups	2020	Planned Spend	0
A	Tables	2020	Extra Spend	2
A	Chairs	2020	Extra Spend	7
A	Forks	2020	Extra Spend	6
A	Knives	2020	Extra Spend	7
A	Napkins	2020	Extra Spend	4
A	Cups	2020	Extra Spend	1
B	Tables	2020	Planned Spend	10
B	Chairs	2020	Planned Spend	15
B	Forks	2020	Planned Spend	20
B	Knives	2020	Planned Spend	5
B	Napkins	2020	Planned Spend	10
B	Cups	2020	Planned Spend	0
B	Tables	2020	Extra Spend	1
B	Chairs	2020	Extra Spend	3.5
B	Forks	2020	Extra Spend	3
B	Knives	2020	Extra Spend	3.5
B	Napkins	2020	Extra Spend	2
B	Cups	2020	Extra Spend	0.5
A	Tables	2021	Planned Spend	19
A	Chairs	2021	Planned Spend	28.5
A	Forks	2021	Planned Spend	38
A	Knives	2021	Planned Spend	9.5
A	Napkins	2021	Planned Spend	19
A	Cups	2021	Planned Spend	0
A	Tables	2021	Extra Spend	1.9
A	Chairs	2021	Extra Spend	6.65
A	Forks	2021	Extra Spend	5.7
A	Knives	2021	Extra Spend	6.65
A	Napkins	2021	Extra Spend	3.8
A	Cups	2021	Extra Spend	0.95
B	Tables	2021	Planned Spend	9.5
B	Chairs	2021	Planned Spend	14.25
B	Forks	2021	Planned Spend	19
B	Knives	2021	Planned Spend	4.75
B	Napkins	2021	Planned Spend	9.5
B	Cups	2021	Planned Spend	0
B	Tables	2021	Extra Spend	0.95
B	Chairs	2021	Extra Spend	3.325
B	Forks	2021	Extra Spend	2.85
B	Knives	2021	Extra Spend	3.325
B	Napkins	2021	Extra Spend	1.9
B	Cups	2021	Extra Spend	0.475
C	Tables	2020	Planned Spend	20
C	Chairs	2020	Planned Spend	30
C	Forks	2020	Planned Spend	40
C	Knives	2020	Planned Spend	22
C	Napkins	2020	Planned Spend	20
C	Cups	2020	Planned Spend	40
C	Tables	2020	Extra Spend	2
C	Chairs	2020	Extra Spend	7
C	Forks	2020	Extra Spend	0
C	Knives	2020	Extra Spend	7
C	Napkins	2020	Extra Spend	3
C	Cups	2020	Extra Spend	1
C	Tables	2021	Planned Spend	19
C	Chairs	2021	Planned Spend	28.5
C	Forks	2021	Planned Spend	38
C	Knives	2021	Planned Spend	9.5
C	Napkins	2021	Planned Spend	11
C	Cups	2021	Planned Spend	38
C	Tables	2021	Extra Spend	1.9
C	Chairs	2021	Extra Spend	6.65
C	Forks	2021	Extra Spend	8
C	Knives	2021	Extra Spend	6.65
C	Napkins	2021	Extra Spend	9
C	Cups	2021	Extra Spend	0.95

[stevedep · Accepted solution]

Hi,

See below:

 'Table'[___AggasPrc] = [___Agg] / CALCULATE(SUM('Table'[Cost]), ALLSELECTED('Table'))
 'Table'[___Rank] = RANKX(SUMMARIZE(ALLSELECTED('Table'),'Table'[Category]), CALCULATE(SUM([Cost])), ,DESC,Dense)
 'Table'[___TopNCost] = IF([___Rank] <= 2, SUM('Table'[Cost]),BLANK())

 'Table'[___Agg] = 
IF(ISFILTERED('Table'[Category]),[___TopNCost],
SUMX(VALUES('Table'[Category]),[___TopNCost]))

As seen here, last column:

Link to file here. 

Pls mark as a solution if so. Thumbs up for the effort are appreciated.

Kind regards,

Steve. 

[Andrey28]

Thanks for the code. This solution works. I am testing it now using an example with the real data.