cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Variance - Actual vs Plan in Matrix

When I add a new measure (in to the value field)  that provides the difference between actual and plan, I'm getting two additon al column

 

This is how i'm calculting variance

Variance = CALCULATE(sum(PB01[Sales]),PB01[Scenario]="Actual")- CALCULATE(sum(PB01[Sales]),PB01[Scenario]="Plan")

 

This is what i'm getting

 

Customer  Actual variance Plan  Variance

Abc Inc         7             3           10          3

 

 

The dataset has the following colums

Customer

Sale_Amount

Scenario : Actual, Plan 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Impactful Individual
Impactful Individual

Re: Variance - Actual vs Plan in Matrix

Simple answer, create three measures - one for actual, one for plan, one for variance

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

View solution in original post

7 REPLIES 7
Highlighted
Impactful Individual
Impactful Individual

Re: Variance - Actual vs Plan in Matrix

Are you putting the scenario column on the report as well?

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂
Highlighted
Regular Visitor

Re: Variance - Actual vs Plan in Matrix

Yes.  This is the setting for the matrix;

 

Row : Customers

Column: Scenario

Value: Sales

 

I tried to add new calculated field ( variance) to the Value and got two additional columns

 

Highlighted
Impactful Individual
Impactful Individual

Re: Variance - Actual vs Plan in Matrix

Cool, you have two values for each scenario, two scenarios * two values = four columns

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂
Highlighted
Regular Visitor

Re: Variance - Actual vs Plan in Matrix

 

Here goes what I'm getting:

 

Customer  Actual variance Plan  Variance

Abc Inc         7             3           10          3

 

 

..But this what I need

 

Customer  Actual Plan  Variance

Abc Inc         7        10          3

Highlighted
Impactful Individual
Impactful Individual

Re: Variance - Actual vs Plan in Matrix

Simple answer, create three measures - one for actual, one for plan, one for variance

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

View solution in original post

Highlighted
Regular Visitor

Re: Variance - Actual vs Plan in Matrix

Thank Austin .. Much appreciated your help

Highlighted
Resolver I
Resolver I

Re: Variance - Actual vs Plan in Matrix

Hi I have a similar problem

 

I am getting

 

                           Qtr1                 |            Qtr2

Product  |      total sales | variance |   total sales | variance

 

I need

 

                           Qtr1  |      Qtr2      |

Product  |      total sales |   total sales | variance

 

can you help ?

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors