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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
60 |
User | Count |
---|---|
197 | |
118 | |
108 | |
78 | |
69 |