Top N Percentage Stress

[Anonymous]

Hi

Starting to tear my hair out with this one.

I have a dataset showing weighings (tonnes) for different materials over the last three financial years.

I want to show the Top 5 Materials within a Multi-Row Card based on their percentage tonnage when compared to all material tonnage for the financial year selected by the slicer.  The problem is that the Top N filter redefines the denominator as the total tonnage for the top 5 material rather than all materials.  The image below shows a summary of the data in question for one financial year, the % values in green that I would like to retain and show after applying the Top N filter, and the % values in amber which are actually what end up being shown :(.

I have searched for other similar problems posted and tried using a measure such as:

DIVIDE( SUM(Table1[weight]), CALCULATE( SUM(Table1[weight]), ALL(Table1) ) )

....... but no joy.  This is because it takes into account all data rather than just weighing dates relevant to the financial year selected in the slicer.  I have also tried changing the ALL function to ALLSELECTED but no success.

I have tried a simple calculated column %GT = Table1[Weight]/sum(Table1[Weight]), but again this does not produce the desired result.

Any help would be greatly appreciated.  Thanks. 

[AlB]

Hi @Anonymous 

1. Place Material and Tonnage in a table visual

2. Place this measure in the visual:

Measure =
VAR top5_ =
    TOPN (
        5,
        ALLSELECTED ( Table1[Material] ),
        CALCULATE ( SUM ( Table1[Tonnage] ), ALLSELECTED ( Table1[Tonnage] ) ), DESC
    )
RETURN
    IF (
        SELECTEDVALUE ( Table1[Tonnage] ) IN top5_,
        DIVIDE (
            SUM ( Table1[Tonnage] ),
            CALCULATE ( SUM ( Table1[Tonnage] ), ALLSELECTED () )
        )
    )

If this does not work please share the pbix (with dummy data if necessary, reproducing the issue)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi, thanks for the reply.  have inputted the measure but getting error message stating that "function 'CONTAINSROW' does not support comparing values of type Text with values of type Number. consider using the VALUE or FORMAT function to convert one of the values"

[AlB]

@Anonymous 

There was a small mistake:

Measure =
VAR top5_ =
    TOPN (
        5,
        ALLSELECTED ( Table1[Material] ),
        CALCULATE ( SUM ( Table1[Tonnage] ), ALLSELECTED ( Table1[Tonnage] ) ), DESC
    )
RETURN
    IF (
        SELECTEDVALUE ( Table1[Material] ) IN top5_,
        DIVIDE (
            SUM ( Table1[Tonnage] ),
            CALCULATE ( SUM ( Table1[Tonnage] ), ALLSELECTED () )
        )
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[mahoneypat · Accepted solution]

Here is one way to do it, using TOPN in your measure instead of as a visual filter.  Change table name from Tonnage to your actual table.

Top 5 New =
VAR vAllMaterials =
    CALCULATE (
        SUM ( Tonnage[Tonnage] ),
        ALLSELECTED ( Tonnage[Material] )
    )
VAR vTop5Mats =
    TOPN (
        5,
        ALLSELECTED ( Tonnage[Material] ),
        CALCULATE (
            SUM ( Tonnage[Tonnage] )
        ), DESC
    )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( Tonnage[Tonnage] ),
            KEEPFILTERS ( vTop5Mats )
        ),
        vAllMaterials
    )

Regards,

Pat

[eastwind_an]

Thank you! I've been searching for solution to display Top 5 values in pie chart based on true percentage and not just percentage of the 5 values. This solution works like a charm.

For pie chart make sure to choose display data value instead of percent of total. 

[Anonymous]

perfect, thanks 🙂

have tried to reply to @AlB but keep getting an error mentioning post flooding??!!