cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Variance calculation in Table or Matrix

Hi everyone

I'm doing a sales comparison report in which the end user can pick any 2 dates in a slicer (2016 and 2021 in below example).

To show the results, I'm trying to achieve this visual made with excel :

In PBI I used a table and a matrix to visualize the results but i can't replicate the excel table above.

1/ TABLE

I went first for a table for which I created 3 measures :

a) SalesTotal_YearOldestCALCULATE([SalesTotal]FILTER(CALENDRIERCALENDRIER[Year]=MIN(CALENDRIER[Year] ))) ----- to show 2016 results

b) SalesTotal_YearMostRecent = CALCULATE([SalesTotal], FILTER(CALENDRIER, CALENDRIER[Year]=MAX(CALENDRIER[Year] ))) ----- to show 2021 results

c) Variance = [SalesTotal_YearMostRecent] - [SalesTotal_YearOldest] / [SalesTotal_YearOldest]

Here's the visual :

This table is ok but i can't find a way to have dynamic column headers : YEAR(oldest) would be 2016 and YEAR(most recent) would be 2021 (based on the years picked in the slicer).

I've read many posts on dynamic column headers but it seems it's not possible for tables (unless you have an idea on how to do it ?).

So i opted for a matrix.

2/ MATRIX

Here's the visual without the Variance measure :

Problem is i can't use the Variance measure in that matrix. The measure won't stay in the "Colonnes" field when i try to drag and drop the measure in. And when i put it in the "Valeurs" field, here's what it does :

Can anyone help me out reproducing the screenshot excel table please ?

Here's the data model if it can be of any help :

I hope my explanations are clear and detailed enough.

Thank you 🙏

1 ACCEPTED SOLUTION
Community Support

Hi @AnthNC ,

Based on your description, I have created a simple example:

Measure =
var _a = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Year]=MIN('Table'[Year])))
var _b = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Year]=MAX('Table'[Year])))
return
IF(ISINSCOPE('Table'[Year]),SUM('Table'[Value]),FORMAT(DIVIDE(_b-_a,_a),"Percent"))

Change the name of  column subtotal:

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Hi @AnthNC ,

Based on your description, I have created a simple example:

Measure =
var _a = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Year]=MIN('Table'[Year])))
var _b = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Year]=MAX('Table'[Year])))
return
IF(ISINSCOPE('Table'[Year]),SUM('Table'[Value]),FORMAT(DIVIDE(_b-_a,_a),"Percent"))

Change the name of  column subtotal:

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regular Visitor

This is a very clear example thanks for that.

Question, I'm computing a numeric rather than percentage variance. In my (very large) dataset there may be instances where the "subcategory" isn't present in a "year". How can I incorporate COALESCE into the above in order to compute a variance where for a given "subcategory", one "year" is BLANK?

I've tried inserting in both the variables and in the IF(INSCOPE line without any success.

 subcategory 2016 2018 Variance a 90 90 b 20 80 60 c 30 70 40

I would've thought this usage of COALESCE would accomplish my objective but I'm stuck:

Measure =
var _a = COALESCE(CALCULATE(SUM('Table'[Value]),FILTER('Table',[Year]=MIN('Table'[Year]))),0)
var _b = COALESCE(CALCULATE(SUM('Table'[Value]),FILTER('Table',[Year]=MAX('Table'[Year]))),0)
return
IF(ISINSCOPE('Table'[Year]),SUM('Table'[Value]),COALESCE(_b,0) - COALESCE(_a,0))
Frequent Visitor

Many thanks Jianbo Li !

I'm finding out about how to use variables in measures. Thanks again

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors