Forum Discussion

AnthNC's avatar
AnthNC
Helper 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
Community 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
    Helper II
    3 years ago

    Many thanks Jianbo Li !

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

  • eddiedee's avatar
    eddiedee
    Regular Visitor
    2 years ago

    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))
  • PBIdashboards's avatar
    PBIdashboards
    Post Patron
    2 months ago

    The dynamic column headers problem you hit is a known limitation native Table visual can't do it, and Matrix requires fixed column definitions. The ISINSCOPE workaround in the accepted solution is the cleanest DAX approach.

    For the end-user date picker + dynamic variance combination you're describing, this is exactly the use case Flexa Tables (AppSource) was built for users select any two periods to compare, variance columns appear automatically with dynamic headers. No DAX needed for the display layer. Search "Flexa Tables" on Microsoft AppSource.