Top 2 Failed Categories

[Beho]

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	Category 4	Category 5
ABC1	1/1/2023	Passed	Passed	Failed	Passed	Failed
DEF2	1/1/2023	Failed	Passed	Failed	Failed	Failed
ABC2	1/1/2023	Failed	Failed	Failed	Failed	Failed
DEF3	3/6/2023	Passed	Passed	Failed	Passed	Failed
ABC3	3/18/2023	Failed	Failed	Passed	Failed	Failed
DEF4	3/18/2023	Failed	Failed	Failed	Passed	Failed
ABC4	5/5/2023	Failed	Passed	Failed	Failed	Failed
DEF5	5/20/2023	Passed	Passed	Failed	Passed	Failed
ABC5	7/18/2023	Failed	Failed	Passed	Failed	Failed
DEF6	9/4/2023	Passed	Passed	Failed	Passed	Failed

The only idea I could think of, is to do measures for each categories and sum the daily counts, then compare in another measure. The list I have has over 10 categories, and I am wondering if there is a way to do it in one measure? I am hoping to be able to have a table or chart by month to show the top 2 failing each month for example.

[v-zhangti]

Hi, @Beho 

You can try the following methods. Check the first two columns-Transform-Unpivot Other column:

Date table:

Measure:

Count = CALCULATE(COUNT('Table'[Name]),FILTER(ALLEXCEPT('Table','Table'[Category]),[Value]="Failed"&&MONTH([Date])=SELECTEDVALUE('Date'[Month])))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Beho]

Hi v-zhangti, My issue is the report has a lot of visuals already set up, once I unpivot, the duplicates mess up the numbers.

Can I create another table and somehow have the dates, names, concatenated column, and then split in that table instead. This way it doesn't touch the original table? 

[v-zhangti]

Hi, @Beho 

Of course. You can copy and paste a copy of your original table in power query. Do the Unpivot operation in the copied data table.

Best Regards

[Beho]

Quick question, if I copy the table, does the new table stay updated? I mean if the original table gets new rows or modified rows, does the copy update automatically? Thanks

[amitchandak]

@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]

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.