How to put accounts calculation on rows and actual/budget calculation on columns?

A dedicated forum to help people when working through the book "Learn to Write DAX"
SamDuoNing
Posts: 1
Joined: Thu Sep 14, 2017 7:14 am

How to put accounts calculation on rows and actual/budget calculation on columns?

Postby SamDuoNing » Thu Sep 14, 2017 8:05 am

Hi,
I am learning Power Pivot and find it's very powerful. However, I have a very simple yet teasing question - related to how to put calculated structure on columns and rows in Power Pivot. I have the data as
Accounts Scenario Amount
Revenue Actual 110
Cost Actual 90
Revenue Budget 100
Cost Budget 85

How do I do the following in Power Pivot?
Actual Budget Variance Variance %
Revenue 110 100 10 10%
Cost 90 85 5 6%
Profit 20 15 5 33%
Margin 18% 15% 3%

Some website said the measures could only be put in Values section of the Power Pivot. So let's assume the Rows are the measures. it's easy to do the following measures and put them in Values section and on rows.
Measure_Reveune:=CALCULATE(SUM(Data[Amount]),Data[Accounts]="Revenue"
Measure_Reveune:=CALCULATE(SUM(Data[Amount]),Data[Accounts]="Cost"
Measure_Profit:=[Measure_Revenue] - [Measure_Cost]
Measure_Margin=DIVIDE([Measure_Profit],[Measure_Revenue])

And then drag the Scenario to columns. This would give me Actual and Budget columns.

What about Variance Column?
The only way I could think of is to use Power Query to manipulate the Data and add a new scenario called Variance in, so the data becomes the following
Accounts Scenario Amount
Revenue Actual 110
Cost Actual 90
Revenue Budget 100
Cost Budget 85
Revenue Variance 10
Cost Variance 80
The issue for this approach is that it would increase the data, if we have lots of rows, this would be a lot. And some of the variance might not be purely adding up the monthly, for example, number of people in a company. If we have a monthly data, we can't just add them up, that would have increase the people 12 fold.

What about Variance%?
Can someone help on this please? I have been searching internet and found very little.One possible (but I don't know) solution is to use MDX to do it structurally? I am not an MDX expert and not sure if this could be easily done?

Regards
Sam Duo Ning

DarbyGloss
Posts: 1
Joined: Fri Nov 17, 2017 8:04 pm

Re: How to put accounts calculation on rows and actual/budget calculation on columns?

Postby DarbyGloss » Fri Nov 17, 2017 8:07 pm

there are a lot of 4g phone jammer you can have a look, and i believe you can find a good one.


Return to “Help with the Book "Learn to Write DAX"”

Who is online

Users browsing this forum: No registered users and 1 guest