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.

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

        Thank you very much for your help!