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:

     

MFelix's avatar
MFelix
Super User
3 years ago

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'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