The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Community,
I have Table as follows.
QA_Score | Error_Category | Provider_ID |
0 | EC1 | 1001-1005 |
0 | EC1 | 1001-1006 |
0 | EC1 | 1001-1007 |
2.5 | EC1 | 1003-1007 |
2.5 | EC1 | 1003-1056 |
2.5 | EC1 | 1004-1002 |
5 | EC1 | 1003-1007 |
5 | EC1 | 1004-1008 |
5 | EC1 | 1004-1009 |
5 | EC1 | 1004-1011 |
7.5 | EC1 | 1004-1012 |
7.5 | EC1 | 1005-1001 |
7.5 | EC2 | 1003-1007 |
7.5 | EC2 | 1004-1002 |
10 | EC1 | 1003-1007 |
10 | EC1 | 1004-1002 |
10 | EC1 | 1004-1011 |
10 | EC1 | 1004-1012 |
12.5 | EC1 | 1016-1001 |
12.5 | EC1 | 1042-1067 |
12.5 | EC2 | 1003-1007 |
15 | EC2 | 1003-1007 |
15 | EC2 | 1004-1002 |
15 | EC2 | 1004-1011 |
15 | EC2 | 1004-1012 |
20 | EC2 | 1003-1007 |
20 | EC2 | 1004-1002 |
25 | EC2 | 1042-1067 |
Here, I am trying to create pareto chart (using Line and clustered column chart) with Error_Category as Shared axis, AVG_QA_Score as Column values, and Pareto% as line values.
I have used the following DAX as per this (7:15 onwards) for the table that I have attached in the screenshot.
Solved! Go to Solution.
@Anonymous
Can you use the below measure instead?
CumulativeAVG_QA_Score =
VAR R= [RankAVG_QA_Score]
RETURN
SUMX(
FILTER(
SUMMARIZE(ALL(Table2),Table2[Error_Category]),
[RankAVG_QA_Score] <= R),
[AVG_QA_Score]
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Awesome @Fowmy Thank you.
You can check the age of the % pair once you want. You must add up to 100% in the last range as shown in this video (Time - 15:00 ).
@Anonymous
Can you try the following measure for the cumulative Average?
CumulativeAVG_QA_Score =
VAR R= [RankAVG_QA_Score]
RETURN
CALCULATE(
[AVG_QA_Score],
FILTER(
SUMMARIZE(ALL(Table2),Table2[Error_Category]),
[RankAVG_QA_Score] <= R)
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
I didn't actually see the video, use the measure for Cumm %
Pareto% =
DIVIDE(
[CumulativeAVG_QA_Score],
CALCULATE(
[CumulativeAVG_QA_Score],
ALLSELECTED(Table2[Error_Category]))
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Fowmy ,
I tried the above formula for cumm %, but it is giving me the required percentage. Please take a look at the shared screenshot. If the link for the pbix is not working, I am sharing some more data below. The formulas for the measures are same as above.
1. In the shared screenshot, the CumulativeAVG_QA_Score should be 29.63+4.38 = 34.01. I am not able to understand why it is showing as 34.00 !!!
2. I need the % to be cumulative. For the 1st rank (EC2), it should be 10.00/38.07 = 26.26 %. Similary for the 2nd rank (EC1), it should be 16.88/38.07 = 44.33%, and 3rd rank (EC3), it should be 23.38/38.07 = 61.41%, and so on. Note: Here, 38.07 is the total of all AVG_QA_Score.
QA_ScoreError_CategoryProvider_ID
0 | EC1 | 1001-1005 |
0 | EC1 | 1001-1006 |
2.5 | EC1 | 1001-1005 |
2.5 | EC1 | 1003-1007 |
5 | EC1 | 1003-1007 |
5 | EC1 | 1004-1008 |
7.5 | EC1 | 1004-1012 |
7.5 | EC1 | 1005-1001 |
10 | EC1 | 1003-1007 |
10 | EC1 | 1004-1002 |
12.5 | EC1 | 1016-1001 |
20 | EC1 | 1042-1067 |
0 | EC2 | 1001-1005 |
0 | EC2 | 1001-1006 |
2.5 | EC2 | 1001-1005 |
2.5 | EC2 | 1003-1007 |
5 | EC2 | 1003-1007 |
5 | EC2 | 1003-1052 |
7.5 | EC2 | 1003-1007 |
7.5 | EC2 | 1004-1002 |
10 | EC2 | 1003-1007 |
10 | EC2 | 1003-1054 |
12.5 | EC2 | 1003-1007 |
12.5 | EC2 | 1004-1002 |
15 | EC2 | 1003-1007 |
15 | EC2 | 1004-1002 |
20 | EC2 | 1003-1007 |
20 | EC2 | 1004-1002 |
25 | EC2 | 1042-1067 |
0 | EC3 | 1001-1005 |
0 | EC3 | 1001-1006 |
2.5 | EC3 | 1003-1007 |
2.5 | EC3 | 1004-1002 |
5 | EC3 | 1003-1007 |
5 | EC3 | 1004-1002 |
10 | EC3 | 1003-1007 |
10 | EC3 | 1003-1054 |
15 | EC3 | 1003-1007 |
15 | EC3 | 1111-1001 |
0 | EC4 | 1001-1005 |
0 | EC4 | 1001-1006 |
2.5 | EC4 | 1003-1007 |
2.5 | EC4 | 1004-1002 |
5 | EC4 | 1003-1007 |
5 | EC4 | 1004-1002 |
10 | EC4 | 1003-1007 |
10 | EC4 | 1004-1002 |
0 | EC5 | 1001-1005 |
0 | EC5 | 1001-1006 |
2.5 | EC5 | 1001-1005 |
2.5 | EC5 | 1001-1006 |
5 | EC5 | 1001-1007 |
5 | EC5 | 1003-1007 |
7.5 | EC5 | 1003-1007 |
7.5 | EC5 | 1004-1011 |
10 | EC5 | 1004-1058 |
10 | EC5 | 1051-1049 |
12.5 | EC5 | 1003-1007 |
12.5 | EC5 | 1004-1058 |
0 | EC6 | 1001-1005 |
0 | EC6 | 1001-1006 |
2.5 | EC6 | 1003-1007 |
2.5 | EC6 | 1004-1011 |
5 | EC6 | 1003-1007 |
5 | EC6 | 1004-1002 |
7.5 | EC6 | 1003-1007 |
10 | EC6 | 1042-1067 |
Please take a look. Thank you.
.
@Anonymous
As per the sample you shared, my results show as below. Error Code EC1 is showing 9.29 which is cumulative.
Share more data and expected result so I can cross-check, You share a sample PBIX file if possible.
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Fowmy We want the cumulative AVG_QA_Score. For the second row, the cumulativeAVG_QA_Score should be like 15.25+5.97, like running total.
Thanks
@Anonymous
Can you use the below measure instead?
CumulativeAVG_QA_Score =
VAR R= [RankAVG_QA_Score]
RETURN
SUMX(
FILTER(
SUMMARIZE(ALL(Table2),Table2[Error_Category]),
[RankAVG_QA_Score] <= R),
[AVG_QA_Score]
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
273 | |
129 | |
123 | |
101 | |
91 |