Forum Discussion
Helper ITop 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 | 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.
6 Replies
- amitchandak
Super User
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-3d98a5b0e07fLearn 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=26448You might have to switch measures based on view by on selection
IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM
BehoI 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.
v-zhangtiCommunity Support
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?
