How to show top 10 values without changing their calculated values

[rahuldas_vgm]

Hello everyone,

I have a matrix visualization which contains 8 months data. When I select the month in slicer, I get the data I want to show. But, when I apply TopN filter the %age calculated measures aggregates the values on the basis of TopN and shows aggregated values on the basis of the top N instead of showing the values with respect to entire data.

Eg:

Branch        Qty       Sales     %GT Sales

A                  10         100           1.11

B                   10         1000        11.11 

C                   10         2000         22.22

D                   10         5000         55.56

E                     10        900           10

My Filter of TopN is by Qty for top3

I want to show:

Branch        Qty       Sales     %GT Sales

B                   10         1000        11.11 

C                   10         2000         22.22

D                   10         5000         55.56

But 

the column %GT values changes and shows as :

%GT Sales

12.5%

25%

62.5%

----------

100%

How can I show the topN values as they are on overall and not on the basis of top3.

Thanks a lot for any help!

Regards,

Rahul

[Ashish_Mathur]

Hi,

Could you share the download link of your PBI file?

[v-yalanwu-msft]

Hi， @rahuldas_vgm ；

You can just create a measure, as follows：

Measure = SUM([Sales])/SUMX(ALL('sales_tab'),[Sales])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[negi007]

@rahuldas_vgm if this resolve your problem, please accept my solution to help others. 

[negi007 · Accepted solution]

@rahuldas_vgm  in this case, there is workaround, you can create a table having topN values

in this case, we have assumed you want to have only top3 value which you can change as per your needs

Table_Top_value =

TOPN(

3,

VALUES(sales_tab[Branch]),

calculate(sum(sales_tab[Sales]))

)

then you can create a calc column in your main table like below to group top and non top values

In_Top = if(isblank(LOOKUPVALUE(Table_Top_value[Branch],Table_Top_value[Branch],sales_tab[Branch])),"not in top3","top3")

then in your visual, you can apply a filter to show only topN values, below is the expected output

 i am attaching pbix file as well. thanks.