Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Help me, Power BI Community. You're my only hope...
I need help with a query that create new columns that would sum Item totals by Area, Team, Group, and all Items and can be filtered by Item, Area, Team, Group, and Order Date.
Group | Team | Area | Date | Item | Item Total | Area Total | Team Total | Group Total | Total |
B | 199CS2 | 3111000766 | 9/29/2022 | 7360774 | 600 | 600 | 4200 | 74099.6 | 109248.84 |
B | 199CS2 | 3111013243 | 9/6/2022 | 7358023 | 3600 | 3600 | 4200 | 74099.6 | 109248.84 |
B | 104CS1 | 3111016966 | 9/14/2022 | 7359081 | 1426.4 | 1426.4 | 1426.4 | 74099.6 | 109248.84 |
B | 113CS1 | 3111005516 | 9/2/2022 | 7363854 | 4522.5 | 22612.5 | 22612.5 | 74099.6 | 109248.84 |
B | 113CS1 | 3111005516 | 9/2/2022 | 7357906 | 18090 | 22612.5 | 22612.5 | 74099.6 | 109248.84 |
C | 104GL1 | 3040000168 | 9/9/2022 | 113975 | 33143.24 | 33143.24 | 33143.24 | 33143.2416 | 109248.84 |
A | 178DW1 | 3020002395 | 9/12/2022 | 170712 | 2006 | 2006 | 2006 | 2006 | 109248.84 |
B | 159CS1 | 3111014799 | 9/16/2022 | 7359315 | 44600.7 | 44600.7 | 44600.7 | 74099.6 | 109248.84 |
B | 109CS1 | 3111017386 | 9/29/2022 | 7360709 | 1260 | 1260 | 1260 | 74099.6 | 109248.84 |
Filter = before 9/29/2022
Group | Team | Area | Date | Item | Item Total | Area Total | Team Total | Group Total | Total |
B | 199CS2 | 3111013243 | 9/6/2022 | 7358023 | 3600 | 3600 | 3600 | 72239.6 | 107388.84 |
B | 104CS1 | 3111016966 | 9/14/2022 | 7359081 | 1426.4 | 1426.4 | 1426.4 | 72239.6 | 107388.84 |
B | 113CS1 | 3111005516 | 9/2/2022 | 7363854 | 4522.5 | 22612.5 | 22612.5 | 72239.6 | 107388.84 |
B | 113CS1 | 3111005516 | 9/2/2022 | 7357906 | 18090 | 22612.5 | 22612.5 | 72239.6 | 107388.84 |
C | 104GL1 | 3040000168 | 9/9/2022 | 113975 | 33143.24 | 33143.24 | 33143.24 | 33143.2416 | 107388.84 |
A | 178DW1 | 3020002395 | 9/12/2022 | 170712 | 2006 | 2006 | 2006 | 2006 | 107388.84 |
B | 159CS1 | 3111014799 | 9/16/2022 | 7359315 | 44600.7 | 44600.7 | 44600.7 | 72239.6 | 107388.84 |
Hello Leia,
This will really need to be done in DAX, rather than Power Query. Try these measures:
_areaSum/Team/Group =
CALCULATE(
SUM(yourTable[Item Total]),
ALL(yourTable),
VALUES(yourTable[Area]) // Just change this to Team/Group for the other totals
)
_totalSum =
CALCULATE(
SUM(yourTable[Item Total]),
ALL(yourTable)
)
Old Ben
Proud to be a Datanaut!
Thanks so much. I tried previously in DAX and Power BI behaved most petulantly. It's entirely possible I was providing faulty language, so I'll test and let you know the output.