Show Only Top N In Column Level

[shareezsaleem]

Hi All,

I have a matrix like below:

Level 1    WBS1 WBS2 WB3.....WBS723

MACHS   12       34       44

PAX         28       44       22

ELEC.       60       32      11

Total       100     110     77

How can I show Top 50 WBS where as by total WBS is 723 (Columns).

Regards

[kushanNa · Accepted solution]

Hi @shareezsaleem 

did you try to use the Top N filter on the filter panel? 

[shareezsaleem]

Thank you.

How can I sort the columns based on the column grand total?

[shareezsaleem]

See the real data

I want to reduce the column to top 5 based on the rank of WBS (which is the sum of values)

WBS Rank Matrix:

WBS Rank = IF(HASONEVALUE(Finance[WBS_Rev.]),RANKX(ALL(Finance[WBS_Rev.]),[Actual MTD],,DESC,Dense))

[AmiraBedh]

You may not be able to achieve what you want in PBI, but here is something that is a workaround.

You need to unpivot your columns :

• Go to Transform Data

• Select your table 

• Select all WBS1 to WBS723 columns

• Right-click > Unpivot Columns

• Rename:

  • Attribute → WBS

  • Value remains as is

From :

To :

Then create 2 measures :

Total Value by WBS = 
CALCULATE(
    SUM('RawData'[Value]),
    ALLEXCEPT('RawData', 'RawData'[WBS])
)

WBS Rank = 
RANKX(
    ALL('RawData'[WBS]),
    [Total Value by WBS],
    ,
    DESC,
    Dense
)

Show Top WBS = 
IF([WBS Rank] <= 50, 1, 0)

The last measure you will use it as a filter on the visual.

[shareezsaleem]

Please reply ..

[Akash_Varuna]

Hi @shareezsaleem You could use DAX to calculate total for each column and then rank them and apply filter in Matrix ..... Please try this out
Calculate Total for Each Column:

TotalValuePerColumn = SUM('Table'[Value])

Replace value with your numerical column
Rank Columns:

RankColumns = RANKX(ALL('Table'[WBS]), [TotalValuePerColumn], , DESC)

Filter Top 50 Columns:
Apply a filter in the matrix visualization to show only columns where RankColumns ≤ 50