Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a division measure that takes Delivery cost divided by a Sales Cost to arrive at a %. I want to calculate the change in % points period vs. period. How do I do this? It feels like I need a measure of a measure?
Using the below example.
Period 1 measure result is 15.1%.
Period 2 measure result is 11.9%
I want to subtract the two periods 15.1% - 11.9% = 3.2%
The visual right now is looking at Quarter 1, but I will eventually make more visuals that may display the full year, one month, or a week. So, I would like to this to be dynamic.
Note: here is the measure I'm currently using:
Hi @Anonymous ,Please follow these steps:
1. Select CA and USD columns and then Use "unpivot" in Query Editor,then data will be transformed like this:
2. Use the formula I refered before,then the output looks like this:
Here is my pbix link:
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi @Anonymous ,
According to my understanding ,you want to calculte the percentage's change based on the same day/month/quarter between years,right?
Here is my data sample:
Then try to use the following formula:
divide values =
DIVIDE (
CALCULATE (
SUM ( 'Delivery date'[Value] ),
FILTER ( 'Delivery date', 'Delivery date'[CA&USD] = "CA" )
),
CALCULATE (
SUM ( 'Delivery date'[Value] ),
FILTER ( 'Delivery date', 'Delivery date'[CA&USD] = "USD" )
)
)
////Year-Quarter for matrix column
year-quarter =
'Delivery date'[Date].[Year] & " " & 'Delivery date'[Date].[Quarter]
totalResults =
IF (
HASONEVALUE ( 'Delivery date'[year-quarter] ),
IF (
HASONEVALUE ( 'Delivery date'[CA&USD] ),
SUM ( 'Delivery date'[Value] ),
[divide values]
),
CALCULATE (
[divide values],
FILTER (
'Delivery date',
'Delivery date'[Date].[Year] = 2019
&& 'Delivery date'[Date].[QuarterNo] = 1
)
)
- CALCULATE (
[divide values],
FILTER (
'Delivery date',
'Delivery date'[Date].[Year] = 2020
&& 'Delivery date'[Date].[QuarterNo] = 1
)
)
)
My visualizations look like this:
Is the result what you want? If not, please upload some data samples and expected output.
Please do mask sensitive data before uploading.
Best Regards,
Eyelyn Qin
Thanks so much. I think yours might work but I'm wondering if there is a simplier approach?
I've taken your example data set and created an example model with link below. I need to add one additional column to show what I was trying to accomplish.
https://1drv.ms/u/s!AvCirXUmRp-JhbY7bLCDvl6fm6HZNA?e=gPcpmv
Using example data set I'm trying to subtract 2.67% (Measure from Q1) - 0.30% (Measure from Q2) = -2.07% difference in quarters
@Anonymous , refer to this, how do this minimum from GuyinaCube
https://www.youtube.com/watch?v=vlnx7QUVYME
Power BI — YTD Questions — Time Intelligence 1–5
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD Questions — Time Intelligence 2–5
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD Questions — Time Intelligence 3–5
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD Questions— Time Intelligence 4–5
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
Power BI — Day Intelligence Questions — Time Intelligence 5–5
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |