Equivalent of Calculated Item in Excel

[bombol]

In excel, you can create "calculated items" inside pivot tables that transcend the specific value that you are aggregating. How can you do that in Power Bi?

I.E. Let's say that you have data in excel that you pivoted, and that there are a bunch of attributes that you can put into pivot rows or columns (not important), and MORE THAN ONE VALUE by which you can compare these row/colum values (i.e. sometimes you take out "cost in $" and replace it with "units" in the values box...or you just keep them both next to each other). Now, let's say I'm analyzing the difference between years 2016 and 2015. All I have to do is 'calculate item' in the pivot table (e.g. name it 'delta'), and define delta as 2016 - 2015 (both variable choices already provided as options to choose when highlighting the 'year' field). What's great about this definition of delta is that no matter what you're summing, whether it's units or $ or rabbits, this delta holds...so if I drag anything in the values box, delta will always be 2016 - 2015 of that anything.

Question: How do you do that in Power BI? All of the posts here seem to suggest that you have to create a measure and then define what to sum. I did that, and it works, but it's very limited since it would require my creating a separate measure for each and every variable that I want to sum/subtract. I.E. I'd have to create a measure for subtracting the sum of $ of 2015 from the sum of $ of 2016, and ANOTHER measure that's just like it but for subtracting the sum of units instead of $. What I want to do is the equivalent of:

Delta = Calculate(sum(anything I put in the values field),year=2016) - calculate(sum(anything I put in the values field),year=2015)

Any help would be greatly appreciated.

Best,

Nabil

[Greg_Deckler]

Yeah, the matric visualization is probably as close as you get out of the box to pivot tables, and they are nowhere close to true pivot tables functionality. There may be a custom visual out there that gets closer.

In your case, you could solve the issue with 2 custom columns and a measure or 3 measures. Essentially, measures 1 and 2 or custom columns 1 and 2 are your equivalent of "Values". You base your third measure on these 2 (either the measures or the columns). Then, if you want to change things up, you just change your two custom columns or measures and your third measure acts exactly as you describe.

[bombol · Accepted solution]

Thanks @Greg_Deckler for confirming my intuition that this currently can't be done in Power BI. Since there's work involved either way anyway, I'll just stick with creating a measure for each variable I want to sum. It's more straightforward and just involves having a bunch of idle measures, versus going in and changing column/measure formulas each time.