Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear all,
Recently I wanted to build a pareto chart with a table from direct query from sql server. I used direct query because
The data is as below:
The master table have rows with same issue, so the first issue is to group all data by issues and calculate the total losstime
with measures:
Then I do a rankx measures:
Solved! Go to Solution.
Hi, @Anonymous
If you want to interact with slicers, you may try replacing 'ALL' with 'ALLSELECTED' to see if it works.
TotalLosstime = SUM('Table'[Losstime])
Ranking1 =
RANKX(
SUMMARIZE(
ALLSELECTED('Table'),
'Table'[Issue],
"Sum",
CALCULATE(
SUM('Table'[Losstime]),
ALLEXCEPT('Table','Table'[Issue])
)
),
[Sum],[TotalLosstime]
)
Cumulative1 =
SUMX(
FILTER(
SUMMARIZE(
ALLSELECTED('Table'),
'Table'[Issue],
"Sum",
CALCULATE(
SUM('Table'[Losstime]),
ALLEXCEPT('Table','Table'[Issue])
)
),
[Issue]<=SELECTEDVALUE('Table'[Issue])
),
[Sum]
)
Cumulative total =
var tab =
SUMMARIZE(
ALLSELECTED('Table'),
'Table'[Issue],
"Sum",SUM('Table'[Losstime])
)
var newtab =
ADDCOLUMNS(
tab,
"Rank",
COUNTROWS(
FILTER(
tab,
[Sum]<=EARLIER([Sum])
)
)+1
)
var t =
ADDCOLUMNS(
newtab,
"Val",
SUMX(
FILTER(
newtab,
[Rank]>=EARLIER([Rank])
),
[Sum]
)
)
return
MAXX(
SUMMARIZE(
'Table',
'Table'[Issue],
"Result",
var _issue = [Issue]
return
SUMX(
FILTER(
t,
[Issue]=_issue
),
[Val]
)
),
[Result]
)
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create measures as below.
TotalLosstime = SUM('Table'[Losstime])
Ranking1 =
RANKX(
SUMMARIZE(
ALL('Table'),
'Table'[Issue],
"Sum",
CALCULATE(
SUM('Table'[Losstime]),
ALLEXCEPT('Table','Table'[Issue])
)
),
[Sum],[TotalLosstime]
)
Cumulative1 =
SUMX(
FILTER(
SUMMARIZE(
ALL('Table'),
'Table'[Issue],
"Sum",
CALCULATE(
SUM('Table'[Losstime]),
ALLEXCEPT('Table','Table'[Issue])
)
),
[Issue]<=SELECTEDVALUE('Table'[Issue])
),
[Sum]
)
Cumulative total =
var tab =
SUMMARIZE(
ALL('Table'),
'Table'[Issue],
"Sum",SUM('Table'[Losstime])
)
var newtab =
ADDCOLUMNS(
tab,
"Rank",
COUNTROWS(
FILTER(
tab,
[Sum]<=EARLIER([Sum])
)
)+1
)
var t =
ADDCOLUMNS(
newtab,
"Val",
SUMX(
FILTER(
newtab,
[Rank]>=EARLIER([Rank])
),
[Sum]
)
)
return
MAXX(
SUMMARIZE(
'Table',
'Table'[Issue],
"Result",
var _issue = [Issue]
return
SUMX(
FILTER(
t,
[Issue]=_issue
),
[Val]
)
),
[Result]
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Allan,
Very thank you for your reply. It does solve the issue for me. Really really appreciate ur help.
I will read through ur solution, as for now, it is quite complicated for me.
Meanwhile, do you know if I want to add in slicer for datetime and production line, how to change the dax code? Now seems like if I filter by slicer, by date and by line, the pareto will be off (over 100%)
I have uploaded my directquery data in excel form for your reference. Hope you can help. You are great. Thank you!
Regards.
Jenas
Data from direct query:
ID | Datetime | Line | Shift | Issue | Losstime |
13 | 1/9/2020 | R1 | 1 | Minor Stop | 111 |
14 | 1/9/2020 | R1 | 1 | Minor Stop | 222 |
15 | 1/9/2020 | R1 | 1 | Minor Stop | 333 |
2 | 6/1/2020 | R1 | 1 | Minor Stop | 490 |
3 | 6/1/2020 | R1 | 1 | Machine Breakdown | 349 |
4 | 6/1/2020 | R1 | 1 | Quality Defect | 343 |
5 | 6/1/2020 | R1 | 1 | Parts Delay | 161 |
6 | 6/1/2020 | R1 | 1 | Others | 109 |
7 | 6/2/2020 | F1 | 2 | Minor Stop | 490 |
8 | 6/2/2020 | F1 | 2 | Machine Breakdown | 349 |
9 | 6/2/2020 | F1 | 2 | Quality Defect | 343 |
10 | 6/2/2020 | F1 | 2 | Parts Delay | 161 |
11 | 6/2/2020 | F1 | 2 | Others | 109 |
16 | 3/9/2020 | R1 | 1 | Minor Stop | 100 |
17 | 4/9/2020 | R1 | 1 | Minor Stop | 100 |
Hi,
You may download my PBI file from here.
Hope this helps.
Hi, @Anonymous
If you want to interact with slicers, you may try replacing 'ALL' with 'ALLSELECTED' to see if it works.
TotalLosstime = SUM('Table'[Losstime])
Ranking1 =
RANKX(
SUMMARIZE(
ALLSELECTED('Table'),
'Table'[Issue],
"Sum",
CALCULATE(
SUM('Table'[Losstime]),
ALLEXCEPT('Table','Table'[Issue])
)
),
[Sum],[TotalLosstime]
)
Cumulative1 =
SUMX(
FILTER(
SUMMARIZE(
ALLSELECTED('Table'),
'Table'[Issue],
"Sum",
CALCULATE(
SUM('Table'[Losstime]),
ALLEXCEPT('Table','Table'[Issue])
)
),
[Issue]<=SELECTEDVALUE('Table'[Issue])
),
[Sum]
)
Cumulative total =
var tab =
SUMMARIZE(
ALLSELECTED('Table'),
'Table'[Issue],
"Sum",SUM('Table'[Losstime])
)
var newtab =
ADDCOLUMNS(
tab,
"Rank",
COUNTROWS(
FILTER(
tab,
[Sum]<=EARLIER([Sum])
)
)+1
)
var t =
ADDCOLUMNS(
newtab,
"Val",
SUMX(
FILTER(
newtab,
[Rank]>=EARLIER([Rank])
),
[Sum]
)
)
return
MAXX(
SUMMARIZE(
'Table',
'Table'[Issue],
"Result",
var _issue = [Issue]
return
SUMX(
FILTER(
t,
[Issue]=_issue
),
[Val]
)
),
[Result]
)
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Try like
Cumm Sales1 = CALCULATE(
SUM( Losstime_Pareto[Losstime] ) ,
filter(ALLSELECTED( Losstime_Pareto), Losstime_Pareto[Issue] <= max(Losstime_Pareto[Issue]))
)
Hi amitchandak ,
Thank you for ur reply.
After I use ur measures, the result will be like:
But the cumulative total should be based on the ranking1 column.
The correct output should be:
Regards.
Jenas
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
79 | |
64 | |
57 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |