Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi, I have a measure to calculate the Volume Impact. But some of the cell in [Volume Sum] is blank.
If the [Volume Sum] is blank, I want it to be set as zero so that it could calculate the volume impact measure correctly.
For example, Product A in 2020-10, the [Volume Impact Total] should be (0-1)*313 = -313 [395 which is incorrect].
For Product B in 2021-09, the [Volume Impact Total] should calculate as (0-1)*34 = -34.
The [Volume Impact Total] should has the same value as [Revenue YoY] for those lines highlighted in red.
How to fix the measure below? Here is the pbix file.
Volume Impact =
IF (
MIN ( 'Calendar'[Date] ) > MAX ( 'Append Table'[Date] ),
CALCULATE (
SUMX (
SUMMARIZE ( 'Append Table', 'Calendar'[YearMonth],'Append Table'[Material_Combine]),
IF (
[Volume Last Year] <= 0,
( [Revenue] - [Revenue Last Year] ),
( [Volume Sum] - [Volume Last Year] ) * [AUSP Last Year]
)),
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)*-1,
SUMX (
SUMMARIZE ( 'Append Table','Calendar'[YearMonth], 'Append Table'[Material_Combine] ),
IF (
[Volume Last Year] <= 0,
( [Revenue] - [Revenue Last Year] ),
( [Volume Sum] - [Volume Last Year] ) * [AUSP Last Year]
)))
Solved! Go to Solution.
Hi @PBI_newuser
I tried, change the blank cell to zero won't help,
But values become correct when I try this,
So you may need to check the code in variable _a.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @PBI_newuser
I tried, change the blank cell to zero won't help,
But values become correct when I try this,
So you may need to check the code in variable _a.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
There are multiple ways to do this, but one way I'd usually go with is to add "+ 0" to your measure. e.g. If I have a measure saying
SUM(sales)
then I'll change it to
SUM(sales) + 0
Please give it a thumbs up if this helps!
User | Count |
---|---|
80 | |
74 | |
62 | |
61 | |
46 |
User | Count |
---|---|
108 | |
97 | |
88 | |
81 | |
61 |