The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I have the simple FACT table "Piece" with the columns "Piece Key" and "Duration". A piece key may occur multiple times (as seen in the left table below). Yet, I want to group by "Piece Key" to get the summed duration per piece (as seen in the right table below; 407436 is summed to 60)
This works fine so far.
The DAX code I use for grouping by "Piece Key" is the following:
Duration SUM =
CALCULATE(
SUMX(
SUMMARIZE('Piece', 'Piece'[Piece Key], "SumDuration", SUM('Piece'[Duration])),
[SumDuration]
)
)
Yet, I want to show the distribution in the sense of frequency per summed duration in a bar chart. But it does not work, I cannot drag & drop my measure with the summed duration by piece key into the x-axis field. How can I do this?
I can only use duration for each row, so it counts the value "30" twice, instead of summing it up to "60" and then counting "1".
Solved! Go to Solution.
Hi @TimmK ,
You need to create a table with data generateseries from 0 to max value (60) for X aixs in column chart.
X aixs =
VAR _SUMMARIZE =
SUMMARIZE (
ALL ( 'Piece' ),
'Piece'[Piece Key],
"SumDuration", SUM ( 'Piece'[Duration] )
)
RETURN
GENERATESERIES ( 0, MAXX ( _SUMMARIZE, [SumDuration] ), 1 )
Measure:
Count =
VAR _SUMMARIZE =
SUMMARIZE (
ALL ( 'Piece' ),
'Piece'[Piece Key],
"SumDuration", SUM ( 'Piece'[Duration] )
)
RETURN
COUNTAX (
FILTER ( _SUMMARIZE, [SumDuration] = MAX ( 'X aixs'[X aixs] ) ),
[Piece Key]
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @TimmK ,
You need to create a table with data generateseries from 0 to max value (60) for X aixs in column chart.
X aixs =
VAR _SUMMARIZE =
SUMMARIZE (
ALL ( 'Piece' ),
'Piece'[Piece Key],
"SumDuration", SUM ( 'Piece'[Duration] )
)
RETURN
GENERATESERIES ( 0, MAXX ( _SUMMARIZE, [SumDuration] ), 1 )
Measure:
Count =
VAR _SUMMARIZE =
SUMMARIZE (
ALL ( 'Piece' ),
'Piece'[Piece Key],
"SumDuration", SUM ( 'Piece'[Duration] )
)
RETURN
COUNTAX (
FILTER ( _SUMMARIZE, [SumDuration] = MAX ( 'X aixs'[X aixs] ) ),
[Piece Key]
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @TimmK,
Why don't you just create an additional table and use its [SUM Duration] column to create the bar chart you need?
Extra Table = SUMMARIZE ( Piece, [Piece Key], "SUM Duration", SUM ( Piece[Duation] ) )
Best Regards,
Alexander
User | Count |
---|---|
16 | |
8 | |
6 | |
6 | |
5 |
User | Count |
---|---|
23 | |
13 | |
13 | |
8 | |
8 |