Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Charticulator vs Matrix - average of the source data

Hi, it seems that Charticulator calculate the average of the data behind the visuals and not the values of the source data.    Example - 58% is the average of 80% (742/932) and 36% (5971/16622), c...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    You may try this measure.

    Measure = var _cost=CALCULATE(SUM('Table'[Cost]),ALLEXCEPT('Table','Table'[System]))
var _budget=CALCULATE(SUM('Table'[Budget]),ALLEXCEPT('Table','Table'[System]))
return 
IF(ISINSCOPE('Table'[Area]),SUM('Table'[ %]),DIVIDE(_cost,_budget))

    I have the subtotals in the matrix showing the mean.

     

    Best Regards,

    Stephen Tao

     

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

 

If you want to get 38%, then your formula has to calculate the numerator of KE plus the numerator of KI, divided by the denominator of KE plus the denominator of KI.

Is it possible to provide some dummy data and expected results?

 

Best Regards,

Stephen Tao

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Stephen,

    Here is the expected outcome and dummy data

    38% is based on Cost/Budget

    Secondly if you drillthough on right bar chart 17 554 it does not give me the expected outcome. However if you rightclick on 80% then it does. Any thoughts?

    Rgds Henrik

     

    ItemAreaCostBudgetSystem %
    A1KE742932OG80 %
    A2KI597116622OG36 %
    A2KI3441037MG33 %
    A4KE10771431MG75 %
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous ,

       

      You may try this measure.

      Measure = var _cost=CALCULATE(SUM('Table'[Cost]),ALLEXCEPT('Table','Table'[System]))
var _budget=CALCULATE(SUM('Table'[Budget]),ALLEXCEPT('Table','Table'[System]))
return 
IF(ISINSCOPE('Table'[Area]),SUM('Table'[ %]),DIVIDE(_cost,_budget))

      I have the subtotals in the matrix showing the mean.

       

      Best Regards,

      Stephen Tao

       

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