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.
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.
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.