Calculating TopN Percentage of Grand Total with Slicer

[JateenK]

Hi all

I've searched high and low but cannot find a solution to the problem of creating a percentage per customer of the sales total that works with data for multiple years (using a slicer).

I've attempted using the following 2x formula's to try getting a total sales per selected year but find issue with both:

- SALESTOTAL ALL = SUMX(ALL('FactData'), 'FactData'[SALESVALUE])

- SALESTOTAL ALLSELECTED = SUMX(ALLSELECTED('FactData'), 'FactData'[SALESVALUE])

AllSelected does not work as it still filters by TopN. Is there a way to use AllSelected but exclude the TopN?

The TopN is utilised by adding the filter to visual. 

 The other method of SelectAll works perfectly in above example and calculates the desired amount and resulting percentage.

However as indicated below, as soon as a slicer option (FinYear) is selected, the calculation does not adjust and the Percentage calc is no longer reflecting accurately as its still calculating based off all the financial years, not the selected one.

I have created a sample file with data for multiple years as reference.

Any help will be greatly appreciated.

Thanks 

PBI Sample File  

[v-jingzhang · Accepted solution]

Hi @JateenK 

You will have to keep the filter on FinYear and remove all other filters, so I use ALLEXCEPT function to realize it, please try the measures below.

TOTAL = CALCULATE(SUM(FactData[SALESVALUE]),ALLEXCEPT(FactData,'Calendar'[FinYear]))

PERC OF TOTAL = DIVIDE(SUM(FactData[SALESVALUE]),[TOTAL],0)

And the result below. Hope this will be helpful.

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it more quickly.

[amitchandak]

@JateenK , I am not sure I got the issue. But I Check the formula It was on all I created a new one and it responded to filter on FY

PERC OF AllSele SALES = DIVIDE(SUM(FactData[SALESVALUE]), [SALESTOTAL ALLSELECTED],0)

For Top You can use TopN or Rank

Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))


City Rank = RANKX(all(Geography[City]),[Sales])
Rank Top 10 1 = sumx(VALUES(Geography[City]),if([City Rank]<=10,[Sales],BLANK()) )
Rank 2nd top = sumx(filter(VALUES(Geography[City]),[City Rank]=2),[Sales] )
	

TOPN

https://databear.com/power-bi-dax-topn-function/

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[JateenK]

Hi @amitchandak 

I had previously attempted that calculation - and while it does respond to the filter, it calculates based on the total of the Top10 sales, not the Sales amount for the Year. IE: it always incorrectly totals to 100%. I have shown the result of that measure on below screenshot as reference.

To highlight the issue, the example shown is using 2019 financial year selected:

Total Sales for 2019 = 1.52m

Top10 Sales for 2019 = 1.24m

Therefore the total percent of Sales should be 81.6%

and while the 1.52m calc is simple to define outside of the Top10 table, trying to calculate that value within the table is proving difficult

[Greg_Deckler]

@JateenK - Can you not just add the SALESVALUE measure/column to the visual again and in the Visualizations pane, click the drop down arrow next on the column in the Values area for this second sales amount. Choose "Show value as" and then Percent of grand total?

The issue you are having is because of your ALL statement for your all sales total all measure. You probably need to use ALLEXCEPT('Table',[Year]) or something like that.

[JateenK]

Hi @Greg_Deckler 

Thanks for the input. The problem with adding a 2nd column SALESVALUE and showing as percent of Grand Total shows the sales value against total of Top 10 Sales - not against the Total Sales filtered by selected FinYear. ie: Percent should not add up to 100% as we are not showing all customers, only top 10.

I have previously attempted adding the ALLEXCEPT FinYear into the equation, but gave the same result as ALLSELECTED most likely due to the fact it is used within a table filtered for TopN which seems to be the complicating factor.