Dynamically Summing Columns

[brtfrschnr]

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

[BA_Pete]

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

[brtfrschnr]

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.