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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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