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.
I have a measure that calculates the % of two other columns (it is Freight as a % of sales). I want to calculate how this has changed over time and difference of the categories versus the grand total from a prior period. I'm unclear how to do this.
I have attached an example data set as well as a picture below of what I'm trying to do.
Example
Category A is 1.2% in 2020
The Grand Total in 2019 was 0.38%
The difference between Category A in 2020 vs. the Grand Total is 0.35% - 1.20% = -0.85%.
How do I calculate this difference?
Example data in PowerBI: https://1drv.ms/u/s!AvCirXUmRp-JhbY86LW-Vr-skR6XPw?e=AmqJYb
Solved! Go to Solution.
Please try this expression to get your desired result.
NewMeasure =
VAR thisyear =
MIN ( DateTable[Year] )
VAR freightpctsalesminus = [Freight % of Sales minus % Change Difference]
VAR PYtotal =
CALCULATE (
[Freight % of Sales],
ALL ( 'Table'[Category] ),
ALL ( DateTable ),
VALUES ( DateTable[Quarter] ),
DateTable[Year] = thisyear - 1
)
RETURN
PYtotal - freightpctsalesminus
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @Anonymous ,
Please refer to the measure below:
Measure =
VAR lastyear =
CALCULATE (
[Freight % of Sales],
ALL ( 'Table'[Category] ),
SAMEPERIODLASTYEAR ( DateTable[Date] )
)
RETURN
lastyear - [Freight % of Sales minus % Change Difference]
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Please try this expression to get your desired result.
NewMeasure =
VAR thisyear =
MIN ( DateTable[Year] )
VAR freightpctsalesminus = [Freight % of Sales minus % Change Difference]
VAR PYtotal =
CALCULATE (
[Freight % of Sales],
ALL ( 'Table'[Category] ),
ALL ( DateTable ),
VALUES ( DateTable[Quarter] ),
DateTable[Year] = thisyear - 1
)
RETURN
PYtotal - freightpctsalesminus
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |