Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
volt26
Frequent Visitor

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 :

 

volt26_0-1718888267376.png

 

How can I achieve this goal? It is quite easy in excel of course but I can't google the right words to find a tutorial to do it in Power BI.

 

I'm using Power BI Desktop.

 

Thank you!

1 ACCEPTED SOLUTION

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:

vzhengdxumsft_0-1719381757576.png

 

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.

View solution in original post

7 REPLIES 7
volt26
Frequent Visitor

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.

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:

vzhengdxumsft_0-1719208667931.png

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:

vzhengdxumsft_1-1719208784565.png

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.

 

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 :

volt26_0-1719320396632.png

 

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

 

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:

vzhengdxumsft_0-1719381757576.png

 

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.

Thank you very much for your help!

volt26
Frequent Visitor

I apologize, I realize I should've given more information about my pbix file. Unfortunately, the numbers you see in my screenshot are not static. They are from a formula that grabs static data from other tables.

Example :
the 1,1 you see in the upper left cell represents how many transactions per day were made in average in that week.

So we can read that in week 1, the second name did 1,2 transactions per day. Then he did 0,2 in week 2. Week 3 shows no number because he wasn't at work or did 0 transactions.

Now these numbers are calculated from other measures and that is were my problem is... I can't quite grasp the concept of making an average with the numbers shows in the table as they are not static but obtained from measures.

 

I'm trying to recreate this challenge with your table. I added a new column named sold and entered transactions :

volt26_1-1718978363615.png

 

Now in Power BI I tried many things but I just can't get your formula to work because it calls static data from the table while I'm trying to get the average of measurments. I think I'm close to my goal...

 

Here is where I am right now :

volt26_0-1718985026363.png

I shared the files on my google drive : https://drive.google.com/file/d/1oljHM1qoQpainfHB8uj3Lzu_miwsGFmh/view?usp=sharing

Thank you for your help!

v-zhengdxu-msft
Community Support
Community Support

Hi @volt26 

 

Please try this:

Here I create a set of sample for your reference:

vzhengdxumsft_0-1718934455438.png

Then add a measure:

MEASURE =
VAR _currentID =
    SELECTEDVALUE ( 'Table'[ID] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Values] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ID] = _currentID )
    )
        / CALCULATE (
            DISTINCTCOUNT ( 'Table'[Num] ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ID] = _currentID )
        )

The result is as follow:

vzhengdxumsft_1-1718934536771.png

 

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.