IF statement Two Measure Wrong Grand Total (we transfert sample link)

[EdouSav]

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

Thanks a lot 

[MFelix]

Hi  @EdouSav ,

This is related with the context transition when you are making the comparision since the total line the value for the 42.802 so its sumt up the same value has in the other column you should do the following measure:

IfNotSalesThenBudget = SUMX(VALUES('Calendar'[Month]),if([TotalProduct]=0,[TotalBudget],[TotalProduct]))

[EdouSav]

Hello @MFelix 

thanks you for your reply

but youre measure doesn't work  with my model, i create a news pbix to simulate the mistake 

the matrix compute 317K but the real number is 318 K

https://we.tl/t-ec1MT7SUjL

if you have another idea

many thanks 

[MFelix · Accepted solution]

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:

[EdouSav]

Dear @MFelix  thanks you for youre help