Forum Discussion

harirao's avatar
harirao
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

Calculating Average Total and Percentage Total

Hi All,

I am working on a Matrix table looking for solution, how find Average total & percentage from Feb to Jun, based on this i have another Donut Chart


 

Donut chart using Total Average from Feb to Jun



Thank you

 

Regards,

Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi harirao ,

     

    I think you in data model [Feb-23]...[Jun-23] should all be columns as below.

    This will make our calculation difficult and I suggest you to try UNPIVOT function to transform your table.

    Select [Item.[DP Manager]] column and unpivot other columns. Then remove blank values in [Value] column.

    For reference: Unpivot columns - Power Query | Microsoft Learn

    Then average measure shoule be as below.

    Count = CALCULATE(COUNT('Table'[Value]))
    Percentage of Total =
VAR _Total =
    CALCULATE ( [Count], ALLEXCEPT ( 'Table', 'Table'[Item.[DP Manager]]] ) )
RETURN
    DIVIDE ( [Count], _Total )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi harirao ,

     

    I think you in data model [Feb-23]...[Jun-23] should all be columns as below.

    This will make our calculation difficult and I suggest you to try UNPIVOT function to transform your table.

    Select [Item.[DP Manager]] column and unpivot other columns. Then remove blank values in [Value] column.

    For reference: Unpivot columns - Power Query | Microsoft Learn

    Then average measure shoule be as below.

    Count = CALCULATE(COUNT('Table'[Value]))
    Percentage of Total =
VAR _Total =
    CALCULATE ( [Count], ALLEXCEPT ( 'Table', 'Table'[Item.[DP Manager]]] ) )
RETURN
    DIVIDE ( [Count], _Total )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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