Using sliced table values in measures

[vijaysw]

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. 

[v-xjiin-msft · Accepted solution]

@vijaysw

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.

[vijaysw]

@v-xjiin-msft

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!

[Greg_Deckler]

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-