Forum Discussion

Beho's avatar
Beho
Icon for Helper I rankHelper I
2 years ago

Top 2 Failed Categories

Hello,   I have a scoring table with multiple categories and I am trying to have visibility on top 2 failed categories per day, month, etc.   Name Date Category 1 Category 2  Category 3 C...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

Beho , If this your source data and the category is on the column, then unpivot it

Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g

 

Now you can have measure

M1= countrows(Table)

 

You can use TopN

 

Calculate([M1], keepfilters(topn(2,allselected(Table[Category]), [M1], desc)))

 

or window

 

Calculate([M1], keepfilters(window(1,abs,2,abs,allselected(Table[Category]),orderby( [M1], desc) ))

 

refer

 

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

 

Learn Power BI: Dynamic TOPN using TOPN/Window and Numeric parameter: https://youtu.be/vm2mdEioQPQ

 

TOPN: https://youtu.be/QIVEFp-QiOk
TOPN with Numeric Parameter -https://youtu.be/cN8AO3_vmlY?t=26448

 

 

You might have to switch measures based on view by on selection

 

IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM

 

 

Beho's avatar
Beho
Icon for Helper I rankHelper I
2 years ago

I tried unpivoting some categories to test, it created a lot of duplicates to my data and it updated all the visuals in my report to the incorrect counts. I will go through the videos, but if it will always create dups, then it would not work.