Calculation works well on line item wise but grand total is coming wrong

[Vaneet05]

Dear Team,

In my workbook, two sheets are there price erosion dashboard and floor price dashboard. In-floor price dashboard column name diff floor price is working fine for line item wise and also for the sum of that column to show the grand total.

Diff Floor Price = SUMX(Invoice,CALCULATE(IF([FY 2022-23 Net Sales without Service Floor Price]<[Updated_NLP],[FY 2022-23 Net Sales without Service Floor Price]-[Updated_NLP],0)))

but in the same case in the price erosion dashboard column name FY 2022-23 price erosion calculation does not work perfectly for the line item when I use sumx in front of that to find grand total for the particular column. The current calculation is:-

FY 2022-23 Price Erosion =

CALCULATE (

IF (

[Actual quantity 2022-23]>0,

([FY 2022-23 ASP] - [FY 2021-22 ASP])*[Actual quantity 2022-23],([FY 2022-23 ASP]-[FY 2021-22 ASP])*[Actual quantity 2021-22]))

which is perfectly working for line item wise but when I use the sumx in front of that to sum the all values for that particular column in the grand total my line item wise values are getting disturbed. Please suggest something for the calculation for line item wise and to sum all values to show the grand total.

https://drive.google.com/file/d/1qh-JM83pILg4MhVXM-_HTwTx4yApAEDN/view?usp=sharing

[v-rongtiep-msft · Accepted solution]

Hi @Vaneet05 ,

Please have a try.

Measure= var _a=[FY 2022-23 Price Erosion]
var _b=SUMMARIZE(Invoice,'Product'[Product_Code1__c],"aaa",Invoice[FY 2022-23 Price Erosion])
RETURN
IF(ISINSCOPE('Product'[Product_Code1__c]),_a,SUMX(all(Invoice),[FY 2022-23 Price Erosion])
)

The error happened because of the data filter. When you remove the date slicer, the data are right.

Or try to change the invoice and calender date relationship direction. I really cannot do any operation on my computer.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vaneet05]

Thankyou It works.

[v-rongtiep-msft]

Hi @Vaneet05 ，

Please have a try. Change the hasonvalue. Because the Product'[Product_Code1__c has more than one value instead of unique value.

Measure =
VAR _a = Invoice[FY 2022-23 Price Erosion]
VAR _b =
    SUMMARIZE (
        Invoice,
        'Product'[Product_Code1__c],
        "aaa", Invoice[FY 2022-23 Price Erosion]
    )
RETURN
    IF ( ISINSCOPE ( 'Product'[Product_Code1__c] ), _a, SUMX ( _b, [aaa] ) )

hasonevalue: Returns TRUE when the context for columnName has been filtered down to one distinct value only. Otherwise is FALSE.

Isinscope: Returns true when the specified column is the level in a hierarchy of levels.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vaneet05]

Dear @v-rongtiep-msft ,

The above calculation is working fine but a new issue has come where my FY net sales Without service column value is zero I am getting the value in my new measure but in the grand total it's not summing up like its skipping the rows where sales value is zero but line item wise we have the value but in grand total, it considers 0 for the new measure. Please suggest something in this case.

[v-rongtiep-msft]

Hi @Vaneet05 ,

I am cannot understand your meaning correctly, please provide your desired output with a screenshot. The measures work well

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vaneet05]

Dear @v-rongtiep-msft ,

I mean to say that the formula you have provided is going well but where my net sales are zero the price erosion for that case is not summing in the grand total of the price erosion column. Line item wise I got the price erosion value but when grand total is calculated it skipped the rows where sales are zero and give grand total value after filtering those rows.

Example :

in Product code 122 my price erosion is -11576 but the sales are front of that is 0, grand total which 35523322 is coming it's not including -11576 means it's not subtracting. and these happen in those cases where net sales without service column values are 0.

[Vaneet05]

Dear @v-rongtiep-msft 

Please suggest

[v-rongtiep-msft]

Hi  @Vaneet05 ,

I cannot download the pbix file.

If I have not misunderstood your meaning, please have a try. Create a measure based on Diff Floor Price 

Measure= var _a=[Diff Floor Price]
var _b=summarize('Invoice',,a column date or index,"aaa",[Diff Floor Price])
return
if(hasonevalue(a column date or index),_a,sumx(_b,[aaa]))

I have also found a blog, please refer to it to see if it helps you.

Dealing with Measure Totals 

If I have misunderstood your meaning, please provide a simple sample and your desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vaneet05]

Dear @v-rongtiep-msft 

Formula i am using right now :

FY 2022-23 Price Erosion =

calculate(if([Actual quantity 2022-23]>0,([FY 2022-23 ASP]-[FY 2021-22 ASP])*[Actual quantity 2022-23],([FY 2022-23 ASP]-[FY 2021-22 ASP])*[Actual quantity 2021-22]))

Should I use the above formula as a base formula in my new calculation suggested by You?

[v-rongtiep-msft]

Hi @Vaneet05 ,

Please have a try.

If I have misunderstood your meaning, please provide a simple sample (the file you provide is too big to download) and your desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vaneet05]

Dear @v-rongtiep-msft 

I am using the new measure 

Measure = var _a=[FY 2022-23 Price Erosion]

var _b=summarize('Product','Product'[Product_Code1__c],"aaa",[FY 2022-23 Price Erosion])

return

if(HASONEVALUE('Product'[Product_Code1__c]),_a,sumx(_b,[aaa]))

you can see for some line item value has vanished. The formula is not working fine.

[Vaneet05]

Dear @v-rongtiep-msft,

My Diff Floor Price is already perfectly fine with this formula but when I use the same calculation for price erosion calculation it's not working fine for that and you can still download my workbook it's available for everyone.

[v-rongtiep-msft]

Hi @Vaneet05 ,

Please have a try.

Measure= var _a=[FY 2022-23 Price Erosion]
var _b=summarize('Invoice',a column date or index,"aaa",[FY 2022-23 Price Erosion])
return
if(hasonevalue(a column date or index),_a,sumx(_b,[aaa]))

If it doesnot work , please provide a screenshoot with the error. 

If I have misunderstood your meaning, please provide a simple sample (the file you provide is too big to download) and your desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.