Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AnthNC
Helper I
Helper I

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).

AnthNC_2-1683261034362.png

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

AnthNC_6-1683263647955.png

 

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 :

AnthNC_3-1683262174545.png

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 :

AnthNC_4-1683262883042.png

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 :

AnthNC_5-1683263319398.png

 

 

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

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

AnthNC_7-1683263974883.png

 

I hope my explanations are clear and detailed enough.

Thank you 🙏

 

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @AnthNC ,

 

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

vjianbolimsft_0-1683514541279.png

Please try:

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"))

vjianbolimsft_1-1683514806671.png

Change the name of  column subtotal:

vjianbolimsft_2-1683514887999.png

Final output:

vjianbolimsft_3-1683514905647.png

vjianbolimsft_4-1683514936668.png

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.

View solution in original post

3 REPLIES 3
v-jianboli-msft
Community Support
Community Support

Hi @AnthNC ,

 

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

vjianbolimsft_0-1683514541279.png

Please try:

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"))

vjianbolimsft_1-1683514806671.png

Change the name of  column subtotal:

vjianbolimsft_2-1683514887999.png

Final output:

vjianbolimsft_3-1683514905647.png

vjianbolimsft_4-1683514936668.png

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.

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.

 

subcategory20162018Variance
a 9090
b208060
c307040

 

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))

Many thanks Jianbo Li !

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.