Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
EdouSav
Helper I
Helper I

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 

 

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

MFelix_0-1686172076452.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User
Super User

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]))

 

MFelix_0-1686000485494.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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 

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_0-1686172076452.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Dear @MFelix  thanks you for youre help 

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.