Forum Discussion

EdouSav's avatar
EdouSav
Helper I
3 years ago
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...
Need Help
  • MFelix's avatar
    MFelix
    3 years ago

    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's avatar
EdouSav
Helper I
3 years ago

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's avatar
MFelix
Super User
3 years ago

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's avatar
    EdouSav
    Helper I
    3 years ago

    Dear MFelix  thanks you for youre help