Forum Discussion

nmck86's avatar
nmck86
Post Patron
9 years ago
Solved

Variance Analysis in Power BI

Hi All,

 

I have a quesiton with how to add a variance number beside the 2 fields that I have listed below. I would like to have a third column that highlights the variance or something that I can add that will let me knwo the difference between the 2 columns.

 

 

Thanks in advance for your assistance.

 

Thanks,

Nichole

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi nmck86,

     

    You can use below measure to calculate the variance , then use "line and clustered column chart" to show the result.

     

    STDEVXP = 
var currType= LASTNONBLANK(Sheet1[Type],[Type])
var temp=STDEVX.P(SUMMARIZE(FILTER(ALL(Sheet1),[Type]=currType),[Type],[Year],"Total",SUM(Sheet1[Amount])),[Total])
return
POWER(temp,2)

     

     

    Regards,

    Xiaoxin Sheng

13 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi nmck86,

     

    You can use below measure to calculate the variance , then use "line and clustered column chart" to show the result.

     

    STDEVXP = 
var currType= LASTNONBLANK(Sheet1[Type],[Type])
var temp=STDEVX.P(SUMMARIZE(FILTER(ALL(Sheet1),[Type]=currType),[Type],[Year],"Total",SUM(Sheet1[Amount])),[Total])
return
POWER(temp,2)

     

     

    Regards,

    Xiaoxin Sheng

    • nmck86's avatar
      nmck86
      Post Patron
      9 years ago

      Is there a way to change that varaince line to the difference and not some other #? what you have is similar to what I am trying to accomplish; however, when I view the image it isnt a difference on the line. For instance for your first line the differnce is +213. The second line is +33, etc. Can you assist with that?

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi nmck86,

         

        The result is the calculate variance of summarized records(group by type), if you only want to know th diff between the summared records, you can try to use below formula:

         

        Diff= 
var currType= LASTNONBLANK(Sheet1[Type],[Type])
var temp=SUMMARIZE(FILTER(ALL(Sheet1),[Type]=currType),[Type],[Year],"Total",SUM(Sheet1[Amount]))
return
MAXX(temp,[Total]) - MINX(temp,[Total])

         

        Regards,

        Xiaoxin Sheng

  • PBIdashboards's avatar
    PBIdashboards
    Post Patron
    2 months ago

    The accepted DAX solution is still valid. One thing worth adding for 2026: if you need variance columns that Finance users can add or switch themselves in the published report without going back to Desktop Flexa Tables on AppSource handles this natively. End-users select which two columns to compare and the variance column (absolute + %) appears instantly.

    Particularly useful when the list of metrics keeps growing and maintaining one DAX measure per variance becomes overhead