Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
Thankyou It works.
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.
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.
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.
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.
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.
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.
Dear @v-rongtiep-msft
Formula i am using right now :
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.
Dear @v-rongtiep-msft
I am using the new measure
you can see for some line item value has vanished. The formula is not working fine.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |