Doing Pivot Table calculations on raw data

[Anonymous]

I have a bunch of raw, unpivoted data over 20Years for franchise locations around the US. I want to look at YTD member conversions (from one membership package to another) for each franchise location.

I'm able to do this across the entire company with the measure for YTD conversions and then PYTD Conversions using the following measures:

YTD Initiates = TOTALYTD(COUNTA('20YearConversions'[Reference ID]),'20YearConversions'[Conversion Date],"06/30")

However, I want this measure to return a column of each locations YTD conversions. How would I go about doing this? In excel, python, etc, I would just make it into a pivot table with fiscal year as the columns, index as the franchise location, and each entry as the total yearly conversions for that location. How do I do this in PowerBI without pivoting the data and calculating that way??

Thanks!!!

[v-diye-msft]

Hi @Anonymous 

You might consider creating pbix file that will contain some sample data, upload the pbix to onedrive or dropbox and share the link to the file. Please do not forget to describe the expected results based on this sample data.

[Anonymous]

Hi @v-diye-msft ,

Thanks for the response.

Essentially, I want to do pivot table type calculations on raw data without needing to pivot the data.  Below is an example of what the data looks like when put into a matrix form. Essentially, all I want to do is create calculations/columns/measures that subtract the total number of conversions in each fiscal year by each franchise to get the YoY difference at each location.  

So for example, the expected result for this type of calculation would be the franchise specific YTD sales minus the YTD sales from last year.  Again, I'm able to do this for all franchises grouped together, but not ona franchise specific basis.

Does this help? I just need a calculation likely with a filter, to get this but I can't figure it out.  

Michael

[v-diye-msft]

Hi @Anonymous 

Please kindly share the data in the table rather than Matrix, and disclose the expected results. that would be easier for us to repro the scenario and provide a valuable solution. looking forward to your reply.