Forum Discussion
Solved
IF statement Two Measure Wrong Grand Total (we transfert sample link)
Hello in my example my measure dont run i want to show : if TotalProduct is null the TotalBudget else TotalProduct at the row its ok but at the grand total it KO https://we.tl/t-gaphp8NYUQ T...
Hi EdouSav ,
This is related with the context when you add more values to your table then the context change in this case the product adds a level of granularity that changes the values, because on the month of october when you have values you do not get the 200 for 68100.
Try the following measure:
IfNotSaleThenBudget2 = var SalesTable = SUMMARIZE ( sales, 'product'[product], 'Calendar'[Year], 'Calendar'[Month Number], "TotalValue", [IfNotSalesThenBudget], "IDColumn", 'product'[product]& 'Calendar'[Year]& 'Calendar'[Month Number] ) var FilterSalesValues = SELECTCOLUMNS(SalesTable, "FilterID", [IDColumn]) return SUMX( union( filter (SUMMARIZE ( budget, 'product'[product], 'Calendar'[Year], 'Calendar'[Month Number], "TotalValue", [IfNotSalesThenBudget], "IDColumn", 'product'[product]& 'Calendar'[Year]& 'Calendar'[Month Number] ), NOT([IDColumn] in FilterSalesValues)) ,SalesTable), [TotalValue])Result below and in attach file:
Hi EdouSav ,
This is related with the context when you add more values to your table then the context change in this case the product adds a level of granularity that changes the values, because on the month of october when you have values you do not get the 200 for 68100.
Try the following measure:
IfNotSaleThenBudget2 =
var SalesTable = SUMMARIZE (
sales,
'product'[product],
'Calendar'[Year],
'Calendar'[Month Number],
"TotalValue", [IfNotSalesThenBudget], "IDColumn",
'product'[product]& 'Calendar'[Year]&
'Calendar'[Month Number]
)
var FilterSalesValues = SELECTCOLUMNS(SalesTable, "FilterID", [IDColumn])
return
SUMX( union(
filter (SUMMARIZE (
budget,
'product'[product],
'Calendar'[Year],
'Calendar'[Month Number],
"TotalValue", [IfNotSalesThenBudget], "IDColumn",
'product'[product]& 'Calendar'[Year]&
'Calendar'[Month Number]
), NOT([IDColumn] in FilterSalesValues))
,SalesTable), [TotalValue])
Result below and in attach file:
