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.
Here is the situation I have:
I have a measure "Percent_win" that I calculate on a table. I then use a table visualization that then slices this measures by categories ( a column in the tabel), say catA, catB etc. So I ahve this;
Category Percent_Win
CatA 70%
CatB 55%
and so on
I have this exact report on 2016 data and exported the above table to CSV. I then imported that data into the 2017 report.I would like to be able to calculate a measure that show the difference Year over Year. A simple measure formula like this:
Yoy = Percent_Win - Pre_Percent_Win
doesnt work becuase there isnt a 1-1correspondence. I am not sure how to achieve that. Any help would be greatly appreciated. Needless to say, I am complete newbie in PowerBI, so apologies if this is trivial.
Solved! Go to Solution.
In your scenario, to achieve your requirement, you can refer to following steps:
1. I suppose source table (PowerTest) is like this:
2. Create a calendar table with expression like: CALENDAR(date(2016,01,01),date(2017,12,31)). And create a relation for this calendar and source table:
3. Create a measure to calculate the Percent-Win:
CALCULATE(COUNTROWS(PowerTest1),FILTER(PowerTest1,PowerTest1[StatusType]="Win"))/COUNTROWS(PowerTest1)
4. Create another measure to get the Yoy:
YOY = IF(CALCULATE([Win],SAMEPERIODLASTYEAR('date'[Date]))=0,0,[Win]-CALCULATE([Win],SAMEPERIODLASTYEAR('date'[Date])))
The result shows like this:
If above doesn't satisfy your requirement, please share us more inforamtion like your source table structure and some sample data.
Thanks,
Xi Jin.
In your scenario, to achieve your requirement, you can refer to following steps:
1. I suppose source table (PowerTest) is like this:
2. Create a calendar table with expression like: CALENDAR(date(2016,01,01),date(2017,12,31)). And create a relation for this calendar and source table:
3. Create a measure to calculate the Percent-Win:
CALCULATE(COUNTROWS(PowerTest1),FILTER(PowerTest1,PowerTest1[StatusType]="Win"))/COUNTROWS(PowerTest1)
4. Create another measure to get the Yoy:
YOY = IF(CALCULATE([Win],SAMEPERIODLASTYEAR('date'[Date]))=0,0,[Win]-CALCULATE([Win],SAMEPERIODLASTYEAR('date'[Date])))
The result shows like this:
If above doesn't satisfy your requirement, please share us more inforamtion like your source table structure and some sample data.
Thanks,
Xi Jin.
Thank you so much for your reply. My source data is not arranged like that but you've certainly given me an option.
Here is how it is currently arranged:
We actually separate our data in completely separate datasets every year ( so at any given time you only bring in 2016 data or 2017 data). Here is what one dataset would look like (say 2017):
Opportunityid Category Result
abc CATA win
pqr CATB loss
xyz CATB win
mno CATC win
So I would simply calculate a win% (as a measure) and put it into a table that slices it by category. My workflow is to take this exact report, rename it and rerun it with 2016 data. All of this works great.
My challenge is that I would like to get the table result from 2016 (sliced by category) into the 2017 report (as previous year results) and simply be able to calculate a measure that show a year-over-year difference in the win%. I was able to export the 2016 result into a CSV file ( manual process which is dissapointing but thats okay) and import it into 2017 report. What I dont understand is how to then use this information to calculate the YoY win% change since the slicing is happening in the table. I suppose I could calculate win% as a separate measure for each category ( CATAwin, CATBwin etc.) but it would be tedious since we have dozens of categories.
I understand that having the data from all the years (2016, 2017 etc) in one report is much easier but I would rather not go down that path unless I have to ( The upstream data pulls and pre processing make this much harder).
Thank you so much for your help!
Please post sample data, I'm not sure I'm following you. But, typically you can do things like that with something like EARLIER. See this article as it shows how to use EARLIER with some filtering:
https://www.linkedin.com/pulse/mean-time-between-failure-mtbf-power-bi-greg-deckler-microsoft-mvp-
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |