Forum Discussion

volt26's avatar
volt26
Icon for Helper I rankHelper I
2 years ago
Solved

Matrix visual with calculated columns : add an average column of the shown columns?

Hello!   I'm quite new to Power BI and I got almost everything I need from my visual but one thing : an average column of the shown columns. Here is what I have so far :     How can I achi...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi volt26 

     

    Sure, you can try this:

    MEASURE =
VAR _vtable =
    SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[ID], 'Table'[Num], "_AVG", [AVG] )
RETURN
    AVERAGEX ( _vtable, [_AVG] )

    The result is as follow:

     

    Best Regards

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

volt26's avatar
volt26
Icon for Helper I rankHelper I
2 years ago

I also realized the average I'm looking for should be the average of all the articles. So that would be :

((10*20)+(6*10)/30)=8.67 per item

The 130 AVG I got in my screenshot is another calculation (60+200)/2
While getting this result also interest me, my main goal is to get the 8.67 result.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi volt26 

     

    Please try this:

    AVG =
VAR _currentID =
    SELECTEDVALUE ( 'Table'[ID] )
VAR _currentNum =
    MAX ( 'Table'[Num] )
VAR _vtable =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table' ),
        "_product", 'Table'[Values] * 'Table'[Vendu]
    )
VAR _vtable2 =
    SUMMARIZE (
        _vtable,
        'Table'[ID],
        'Table'[Num],
        "_Sum",
            SUMX (
                FILTER ( _vtable, [ID] = EARLIER ( [ID] ) && [Num] = EARLIER ( [Num] ) ),
                [_product]
            )
    )
RETURN
    SUMX (
        FILTER (
            _vtable2,
            [ID] = SELECTEDVALUE ( 'Table'[ID] )
                && [Num] = MAX ( 'Table'[Num] )
        ),
        [_Sum]
    )
        / SUMX (
            CALCULATETABLE (
                VALUES ( 'Table'[Vendu] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[ID] = _currentID
                        && 'Table'[Num] = _currentNum
                )
            ),
            [Vendu]
        )

    The result is 8.67:

    If you want 130, please change the return into:

    SUMX (
    FILTER (
        _vtable2,
        [ID] = SELECTEDVALUE ( 'Table'[ID] )
            && [Num] = MAX ( 'Table'[Num] )
    ),
    [_Sum]
)
    / CALCULATE (
        COUNT ( 'Table'[Num] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[ID] = _currentID
                && 'Table'[Num] = _currentNum
        )
    )

    The result is as follow:

    Best Regards

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

     

    • volt26's avatar
      volt26
      Icon for Helper I rankHelper I
      2 years ago

      Wow! What you did is fantastic!
      I'll push my luck and ask an extra question but if this should be in another thread simply tell me I will do so. I'm aware that you probably want me to click the accept as solution button by now! 🙂

       

      So...

      Now that I have the AVG for each selected cycle, is it possible to have a column that will show the average of the averages? Like this :

       

      (sum of all selected Num columns)/(amount of Num columns selected) = Average of averages
      (4+7+1+8.67+9)/5=5.934

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi volt26 

         

        Sure, you can try this:

        MEASURE =
VAR _vtable =
    SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[ID], 'Table'[Num], "_AVG", [AVG] )
RETURN
    AVERAGEX ( _vtable, [_AVG] )

        The result is as follow:

         

        Best Regards

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