Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi
My dashboard is to measure the production of KG's packed, per person, per day in a factory.
My data looks like this:
Date | Person | Real Weight |
01-Jan-22 | Person A | 1 |
01-Jan-22 | Person A | 1.5 |
01-Jan-22 | Person A | 1 |
01-Jan-22 | Person A | 1.6 |
01-Jan-22 | Person A | 1 |
01-Jan-22 | Person B | 3.5 |
01-Jan-22 | Person B | 3 |
01-Jan-22 | Person B | 1.5 |
01-Jan-22 | Person B | 2 |
02-Jan-22 | Person A | 1.5 |
02-Jan-22 | Person A | 1 |
02-Jan-22 | Person A | 1 |
02-Jan-22 | Person A | 0.5 |
02-Jan-22 | Person B | 1 |
02-Jan-22 | Person B | 2 |
I Need to get it as follows:
Date | Person | Real Weight | Cumulative Weight |
01-Jan-22 | Person A | 1 | 1 |
01-Jan-22 | Person A | 1.5 | 2.5 |
01-Jan-22 | Person A | 1 | 3.5 |
01-Jan-22 | Person A | 1.6 | 5.1 |
01-Jan-22 | Person A | 1 | 6.1 |
01-Jan-22 | Person B | 3.5 | 3.5 |
01-Jan-22 | Person B | 3 | 6.5 |
01-Jan-22 | Person B | 1.5 | 8 |
01-Jan-22 | Person B | 2 | 10 |
02-Jan-22 | Person A | 1.5 | 1.5 |
02-Jan-22 | Person A | 1 | 2.5 |
02-Jan-22 | Person A | 1 | 3.5 |
02-Jan-22 | Person A | 0.5 | 4 |
02-Jan-22 | Person B | 1 | 1 |
02-Jan-22 | Person B | 2 | 3 |
I then have a futher requirement, where I need to know how much was below and how much over 4 KG.
So my table needs to expand to this
Date | Person | Real Weight | Cumulative Weight | Below 4 | Above 4 |
01-Jan-22 | Person A | 1 | 1 | 1 | |
01-Jan-22 | Person A | 1.5 | 2.5 | 1.5 | |
01-Jan-22 | Person A | 1 | 3.5 | 1 | |
01-Jan-22 | Person A | 1.6 | 5.1 | 0.5 | 1.1 |
01-Jan-22 | Person A | 1 | 6.1 | 1 | |
01-Jan-22 | Person B | 3.5 | 3.5 | 3.5 | |
01-Jan-22 | Person B | 3 | 6.5 | 0.5 | 2.5 |
01-Jan-22 | Person B | 1.5 | 8 | 1.5 | |
01-Jan-22 | Person B | 2 | 10 | 2 | |
02-Jan-22 | Person A | 1.5 | 1.5 | 1.5 | |
02-Jan-22 | Person A | 1 | 2.5 | 1 | |
02-Jan-22 | Person A | 1 | 3.5 | 1 | |
02-Jan-22 | Person A | 0.5 | 4 | 0.5 | |
02-Jan-22 | Person B | 1 | 1 | 1 | |
02-Jan-22 | Person B | 2 | 3 | 2 |
I have tried about 5 different ways that I could see on the forum, so thanks to those who's answered a similar problem. I have however not found one that gives me the answer I require.
If any of you can assist, it will be much appreciated !
Thanks
Roelf
Solved! Go to Solution.
Hi @roelf ,
According to your description, here's my solution.
Add an index column in Power Query, then create three calculated columns.
Cumulative Weight =
SUMX (
FILTER (
'Table',
'Table'[Date] = EARLIER ( 'Table'[Date] )
&& 'Table'[Person] = EARLIER ( 'Table'[Person] )
&& 'Table'[Index] <= EARLIER ( 'Table'[Index] )
),
'Table'[Real Weight]
)
Above 4 =
VAR _Cum =
MAXX (
FILTER (
'Table',
'Table'[Date] = EARLIER ( 'Table'[Date] )
&& 'Table'[Person] = EARLIER ( 'Table'[Person] )
&& 'Table'[Cumulative Weight] > 4
&& 'Table'[Cumulative Weight] < EARLIER ( 'Table'[Cumulative Weight] )
),
'Table'[Cumulative Weight]
)
RETURN
IF (
[Cumulative Weight] > 4,
[Cumulative Weight]
- IF ( _Cum = BLANK (), 4, _Cum )
)
Below 4 =
IF (
[Cumulative Weight] < 4,
'Table'[Real Weight],
IF (
'Table'[Real Weight] - 'Table'[Above 4] = 0,
BLANK (),
'Table'[Real Weight] - 'Table'[Above 4]
)
)
Get the correct result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you all for your suggestions - they all worked 100%
What I ended up using:
- Sorted the data in the Power Query
- Then added an index
- Then used the formula given to get the cumulative amount
I have also checked out other examples which suggesting using "RANKX" as a method instead of adding an index column - not sure which is better. I personally preferred adding an index in the query so that I am sure all rows have values.
Thanks again all - much appreciated !!
Hi @roelf ,
According to your description, here's my solution.
Add an index column in Power Query, then create three calculated columns.
Cumulative Weight =
SUMX (
FILTER (
'Table',
'Table'[Date] = EARLIER ( 'Table'[Date] )
&& 'Table'[Person] = EARLIER ( 'Table'[Person] )
&& 'Table'[Index] <= EARLIER ( 'Table'[Index] )
),
'Table'[Real Weight]
)
Above 4 =
VAR _Cum =
MAXX (
FILTER (
'Table',
'Table'[Date] = EARLIER ( 'Table'[Date] )
&& 'Table'[Person] = EARLIER ( 'Table'[Person] )
&& 'Table'[Cumulative Weight] > 4
&& 'Table'[Cumulative Weight] < EARLIER ( 'Table'[Cumulative Weight] )
),
'Table'[Cumulative Weight]
)
RETURN
IF (
[Cumulative Weight] > 4,
[Cumulative Weight]
- IF ( _Cum = BLANK (), 4, _Cum )
)
Below 4 =
IF (
[Cumulative Weight] < 4,
'Table'[Real Weight],
IF (
'Table'[Real Weight] - 'Table'[Above 4] = 0,
BLANK (),
'Table'[Real Weight] - 'Table'[Above 4]
)
)
Get the correct result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@roelf , Add an index column in power query first and then try a new column
cumm = sumx(filter(Table, [Person] = earlier([Person]) && [Index] <= earlier([index]) ), [Weight])
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
96 | |
90 | |
79 | |
67 |
User | Count |
---|---|
153 | |
125 | |
114 | |
111 | |
95 |