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

Thanks a lot 

 

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:

     

4 Replies

  • 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: