I stack now for 3 days and cannot solve the issue -
I have PB model where I need to calculate certain values, I created a measure (A+B)*C, it works fine for month dimensions but absolutely wrong with year totals. All the data are from one table. I separated month results from totals, but to get totals I've tried summarize, calculation and all the possible combinations of filters. I repeated variable calc for total. Nothing worked. Finally I got the result good for month for subtotals,totals and items (product subcategory), but for totals cannot get item results , totals and subtotals are ok.
Would someone help with this?
Here is the code I am using:
measure1 =
VAR A =
CALCULATE (
SUM ( 'Tb1'[Value] ),
ALL ( 'Tb1'[Products] ),
'Tb1'[Item] = "Item1"
)
VAR B =
CALCULATE (
SUM ( 'Tb1'[Value] ),
ALL ( 'Tb1'[inc/exp] ),
'Tb1'[Item] = "Item2"
)
VAR C =
CALCULATE (
SUM ( 'Tb1'[Value] ),
ALL ( 'Tb1'[Inc/exp] ),
'Tb1'[Item] = "Item3"
)
VAR tb2 =
CALCULATETABLE (
SUMMARIZE (
'Tb1',
'Tb1'[Month],
"result",
(
CALCULATE (
SUM ( 'Tb1'[Value] ),
ALL ( 'Tb1'[inc/exp] ),
'Tb1'[Item] = "Item2"
)
+ CALCULATE ( SUM ( 'Tb1'[Value] ), 'Tb1'[Item] = "Item3" )
)
* CALCULATE ( SUM ( 'Tb1'[Value] ), 'Tb1'[Item] = "Item1" )
),
REMOVEFILTERS ( 'Tb1'[inc/exp], 'Tb1'[Item] )
)
RETURN
IF (
HASONEVALUE ( 'Tb1'[Month] ),
( Item2 + Item3 ) * Item1,
SUMX ( Tb2, [result] )
)
question from:
https://stackoverflow.com/questions/65861639/power-bi-measure-totals 与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
