Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

How to add variance column to matrix

Hi.  Power BI Gurus.    Would appreciate your advice on how to create a matrix table like the following: Year to date May Actual Budget Prior Year Actual vs Budget Actual vs Prior Year ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    Create a copy of that query, do the transformation in the copied query.

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Maggie.  The solution does not meet my requirement as:

- I need to keep the period & country selection flexible

- there are other charts which use the same dataset

 

Pls see my sample file in this link:

https://www.dropbox.com/s/v1zv1hycllbeqco/test.pbix?dl=0

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous 

Create a copy of that query, do the transformation in the copied query.

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • PBIdashboards's avatar
    PBIdashboards
    Icon for Post Patron rankPost Patron
    2 months ago

    For this exact P&L layout (Actual | Budget | Prior Year | Actual vs Budget % | Actual vs Prior Year %), the cleanest DAX pattern when your data has a Version column:

     
     

    Actual = CALCULATE(SUM(Table[Sales]), Table[Version] = "Actual")
    Budget = CALCULATE(SUM(Table[Sales]), Table[Version] = "Budget")
    Prior Year = CALCULATE(SUM(Table[Sales]), Table[Version] = "Prior Year")

    Actual vs Budget % = DIVIDE([Actual] - [Budget], ABS([Budget]))
    Actual vs Prior Year % = DIVIDE([Actual] - [Prior Year], ABS([Prior Year]))

    Note: with 5 metrics (Sales, COGS, Gross Margin, OpEx, PBT), this pattern generates 15 measures minimum. Each new metric adds 3 more.

    For Finance teams who need this layout to stay flexible after publishing  swap periods, add new variance columns  Flexa Tables on AppSource handles all 5 columns as built-in buttons, no DAX