Forum Discussion

AnthNC's avatar
AnthNC
Icon for Helper II rankHelper II
3 years ago
Solved

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 vis...
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi AnthNC ,

     

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

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

    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.

v-jianboli-msft's avatar
v-jianboli-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi AnthNC ,

 

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

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

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.

AnthNC's avatar
AnthNC
Icon for Helper II rankHelper II
3 years ago

Many thanks Jianbo Li !

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