Top N Matrix Problem

[Anonymous]

I'm working on creating a matrix that will look something like this:

Branch Name    Monthly Sales   % Of Grand Total      # Of Sales People

Branch 4                    $444,444                  36.36%                               12

Branch 3                    $333,333                  27.27%                               12

Branch 2                    $222,222                  18.18%                               12

Branch 1                    $111,111                    9.09%                               12

Total Top 4              $1,111,110                  90.90%                              48    

Other Branches         $111,110                    9.09%                               42

Total All Branches   $1,222,220                 100.00%                              90

(I figured up the above numbers manually so I know the final percentage adds up to 99.99% but that's due to my rounding)

After doing some research and watching some videos I figured out how to sort of get the Top 4 part to work using the Top N filter but I end up with a couple of snags.  First, here is what I get back:

Branch Name    Monthly Sales   % Of Grand Total      # Of Sales People

Branch 4                    $444,444                  40.00%                               12

Branch 3                    $333,333                  30.00%                               12

Branch 2                    $222,222                  20.00%                               12

Branch 1                    $111,111                  10.00%                               12

Total Top 4              $1,111,110                 100.00%                              48    

I have a table for measures named ReportMeasures.  I have a measure that looks like the following:

Sum_Total_Sales = SUM(BRANCH_SALES[TOTAL_SALES]).  If I put this measure into its own visual it displays $1,222,220.

The Monthly Sales column is 'ReportMeasures'[Sum_Total_Sales].

The % Of Grand Total column is % of grand total for 'ReportMeasures'[Sum_Total_Sales].  That was done by using the Show Value as menu option and selecting % of grand total.

Technically, it's working correctly except that it's incorrect.  The problem I'm having is that the % Of Grand Total column is basing its calculations on the total monthly sales of the top 4 branches, $1,111,110, instead of the real grand total amount which is $1,222,220.  I have spent most of today and yesterday trying to figure out how to make a measure and/or column that will look at the real grand total and give me the correct values.

I'm hoping that if someone out there can point me in the right direction on this part then I should be able to figure out the rest.

Thanks in advance,

Scott

[amitchandak · Accepted solution]

@Anonymous , if need grand total to owner the filters

% of Total= Divide(SUM(BRANCH_SALES[TOTAL_SALES]), calculate(SUM(BRANCH_SALES[TOTAL_SALES]), allselected()) )

filter should not be consider 

% of Total= Divide(SUM(BRANCH_SALES[TOTAL_SALES]), calculate(SUM(BRANCH_SALES[TOTAL_SALES]), all()) )

Prefer TOPN as visual level filter

TOPN: https://youtu.be/QIVEFp-QiOk

refer if needed

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

[Ashish_Mathur · Accepted solution]

Hi,

Do these measures work?

Monthly sales = SUM(BRANCH_SALES[TOTAL_SALES])

Montly sales of all branches = calculate([Monthly sales],all(BRANCH_SALES[Branch Name]))

% of Grand total = divide([Monthly sales],[Montly sales of all branches])

Hope this helps.

[Anonymous]

Thank you very much for your help.  That was exactly what I needed.

[Ashish_Mathur]

You are welcome.

[amitchandak · Accepted solution]

@Anonymous , if need grand total to owner the filters

% of Total= Divide(SUM(BRANCH_SALES[TOTAL_SALES]), calculate(SUM(BRANCH_SALES[TOTAL_SALES]), allselected()) )

filter should not be consider 

% of Total= Divide(SUM(BRANCH_SALES[TOTAL_SALES]), calculate(SUM(BRANCH_SALES[TOTAL_SALES]), all()) )

Prefer TOPN as visual level filter

TOPN: https://youtu.be/QIVEFp-QiOk

refer if needed

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

[Anonymous]

I'll have to check out the TOPN function since I think I will need it for the next part of my report.

Thanks!