Forum Discussion

Schwami's avatar
Schwami
New Member
3 years ago
Solved

Correlation Factor on Bar Chart

I have the following simplified table. 

 

I can put this on a scatter plot to show some correlation. 


And I can create a correlation measure (quick measure or not). And that all works fine. 


Where I'm running intoo issues is the following. I would like to plot this correlation coefficient onto a bar chart with my department category, but that doesn't seem to be working. 

 



What am I overlooking here? 

Can anybody assist. 




Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Schwami 

    According to your description and dax code , the _mX and _mY  also return the current Label Department's value, so after the calculation it also return 0 .

    var _mX = CALCULATE( AVERAGE('Test 2'[Number of Employees]))
    var _mY = CALCULATE( AVERAGE('Test 2'[Number of Employees left]))
     
    You can try to use this dax code to get the total average of the current Label Department.

    Correlation Coefficient =
    var _mX = CALCULATE( AVERAGE('Test 2'[Number of Employees]),ALL('Test 2'[Label Department]))
    var _mY = CALCULATE( AVERAGE('Test 2'[Number of Employees left]),ALL('Test 2'[Label Department]))
    var _Num = SUMX('Test 2',('Test 2'[Number of Employees]-_mX)* ([Number of Employees left]-_mY))
    var _test =SUMX('Test 2',([Number of Employees] - _mX)^2)*SUMX('Test 2',([Number of Employees left]-_mY)^2)
    var _Den =SQRT(
    SUMX('Test 2',([Number of Employees] - _mX)^2)*SUMX('Test 2',([Number of Employees left]-_mY)^2))
    return
    DIVIDE(_Num,_Den)

     

    You can also put this measure on the Table visual and update the return value to test , like this:
    return
    _Num

     

    So you can find which parameter return the wrong value in your measure.

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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

1 Reply

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

    Hi , Schwami 

    According to your description and dax code , the _mX and _mY  also return the current Label Department's value, so after the calculation it also return 0 .

    var _mX = CALCULATE( AVERAGE('Test 2'[Number of Employees]))
    var _mY = CALCULATE( AVERAGE('Test 2'[Number of Employees left]))
     
    You can try to use this dax code to get the total average of the current Label Department.

    Correlation Coefficient =
    var _mX = CALCULATE( AVERAGE('Test 2'[Number of Employees]),ALL('Test 2'[Label Department]))
    var _mY = CALCULATE( AVERAGE('Test 2'[Number of Employees left]),ALL('Test 2'[Label Department]))
    var _Num = SUMX('Test 2',('Test 2'[Number of Employees]-_mX)* ([Number of Employees left]-_mY))
    var _test =SUMX('Test 2',([Number of Employees] - _mX)^2)*SUMX('Test 2',([Number of Employees left]-_mY)^2)
    var _Den =SQRT(
    SUMX('Test 2',([Number of Employees] - _mX)^2)*SUMX('Test 2',([Number of Employees left]-_mY)^2))
    return
    DIVIDE(_Num,_Den)

     

    You can also put this measure on the Table visual and update the return value to test , like this:
    return
    _Num

     

    So you can find which parameter return the wrong value in your measure.

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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