Matrix: Averaging Column instead of Total(SUM)

[Anonymous]

Hello Everyone, 

I'm new to Power BI and trying to achieve Total average in a matrix table. Below is th sample data:

Product	State	Customer	RFI_HOURS	RFP_HOURS	RFQ_HOURS
A	IL	MED	5	10
B	CA	TESLA	3	8	10
C	NY	BMW		5
D	TX	BENZ	6	8	8
A	CO	HONDA	3		6
B	CA	TESLA	3	2	1
B	MI	FORD		5	5
B	CA	JAGUAR	1	5	4

RESULT: 

Product	AVG_RFI	AVG_RFP	AVG_RFQ
A	4	10	6
B	3.5	6..67	6.67
C		5
D	6	8	8
Totalavg	4.5	7.666667	6.89

But in PBI I see the total avg values are bit off :

Product	AVG_RFI	AVG_RFP	AVG_RFQ
A	4	10	6
B	3.5	6..67	6.67
C		5
D	6	8	8
Totalavg	4.2	7.17	6.8

I have attached PBI file_Avg  with sample data for your reference. Formula I'm using to summarize the data by creating new table.

DAX Formula : 

Table = SUMMARIZE(Sheet1,Sheet1[Product],Sheet1[State],Sheet1[Customer],"avg_RFI",SUM(Sheet1[RFI_HOURS]),"avg_RFP",SUM(Sheet1[RFP_HOURS]),"avg_RFQ",SUM(Sheet1[RFQ_HOURS]))

Questions:

I'm trying to figure the error ?

Is it a right approach for the Total_avg by column ?

Also, How can we  dispaly Total_avg by row?

Thanks in advance. Appreciate for your help !!!

[v-gizhi-msft · Accepted solution]

Hi,

Please take following steps:

1)Try to create a new column:

Customer&State = 'Table'[Customer]&"-"&'Table'[State]

2)Try these three measures:

Avg_RFI = 
AVERAGEX (
    DISTINCT ( 'Table'[Product] ),
    CALCULATE (
        SUM ( 'Table'[RFI_HOURS] )
            / CALCULATE (
                DISTINCTCOUNT ( 'Table'[Customer&State] ),
                FILTER ( 'Table', 'Table'[RFI_HOURS] <> BLANK () )
            )
    )
)

Avg_RFP =
AVERAGEX (
    DISTINCT ( 'Table'[Product] ),
    CALCULATE (
        SUM ( 'Table'[RFP_HOURS] )
            / CALCULATE (
                DISTINCTCOUNT ( 'Table'[Customer&State] ),
                FILTER ( 'Table', 'Table'[RFP_HOURS] <> BLANK () )
            )
    )
)

Avg_RFQ =
AVERAGEX (
    DISTINCT ( 'Table'[Product] ),
    CALCULATE (
        SUM ( 'Table'[RFQ_HOURS] )
            / CALCULATE (
                DISTINCTCOUNT ( 'Table'[Customer&State] ),
                FILTER ( 'Table', 'Table'[RFQ_HOURS] <> BLANK () )
            )
    )
)

3)The result shows:

Note that the result 7.66 in your posted expected result screenshot is not correct, (10+6.67+5+8)=7.42 is the right average result.

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto

[v-gizhi-msft · Accepted solution]

Hi,

Just now i find another easier way to reach your requirement.

Please take following steps:

1)Replace all null value in original table with 0.

2)Unpivot [RFI_HOURS], [RFP_HOURS] and [RFQ_HOURS] columns.

3)After Apply&Close, create this column first:

Customer&State = 'Table (2)'[Customer]&"-"&'Table (2)'[State]

4)Try this measure:

Measure = 
AVERAGEX (
    GROUPBY ( 'Table (2)', 'Table (2)'[Product], 'Table (2)'[Attribute] ),
    CALCULATE (
        DIVIDE (
            SUM ( 'Table (2)'[Value] ),
            CALCULATE (
                DISTINCTCOUNT ( 'Table (2)'[Customer&State] ),
                'Table (2)'[Value] <> BLANK ()
            ),
            BLANK ()
        )
  ))

5)The result shows:

Here is my changed pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto

[v-gizhi-msft · Accepted solution]

Hi,

Just now i find another easier way to reach your requirement.

Please take following steps:

1)Replace all null value in original table with 0.

2)Unpivot [RFI_HOURS], [RFP_HOURS] and [RFQ_HOURS] columns.

3)After Apply&Close, create this column first:

Customer&State = 'Table (2)'[Customer]&"-"&'Table (2)'[State]

4)Try this measure:

Measure = 
AVERAGEX (
    GROUPBY ( 'Table (2)', 'Table (2)'[Product], 'Table (2)'[Attribute] ),
    CALCULATE (
        DIVIDE (
            SUM ( 'Table (2)'[Value] ),
            CALCULATE (
                DISTINCTCOUNT ( 'Table (2)'[Customer&State] ),
                'Table (2)'[Value] <> BLANK ()
            ),
            BLANK ()
        )
  ))

5)The result shows:

Here is my changed pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto

[v-gizhi-msft · Accepted solution]

Hi,

Please take following steps:

1)Try to create a new column:

Customer&State = 'Table'[Customer]&"-"&'Table'[State]

2)Try these three measures:

Avg_RFI = 
AVERAGEX (
    DISTINCT ( 'Table'[Product] ),
    CALCULATE (
        SUM ( 'Table'[RFI_HOURS] )
            / CALCULATE (
                DISTINCTCOUNT ( 'Table'[Customer&State] ),
                FILTER ( 'Table', 'Table'[RFI_HOURS] <> BLANK () )
            )
    )
)

Avg_RFP =
AVERAGEX (
    DISTINCT ( 'Table'[Product] ),
    CALCULATE (
        SUM ( 'Table'[RFP_HOURS] )
            / CALCULATE (
                DISTINCTCOUNT ( 'Table'[Customer&State] ),
                FILTER ( 'Table', 'Table'[RFP_HOURS] <> BLANK () )
            )
    )
)

Avg_RFQ =
AVERAGEX (
    DISTINCT ( 'Table'[Product] ),
    CALCULATE (
        SUM ( 'Table'[RFQ_HOURS] )
            / CALCULATE (
                DISTINCTCOUNT ( 'Table'[Customer&State] ),
                FILTER ( 'Table', 'Table'[RFQ_HOURS] <> BLANK () )
            )
    )
)

3)The result shows:

Note that the result 7.66 in your posted expected result screenshot is not correct, (10+6.67+5+8)=7.42 is the right average result.

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto

[Anonymous · Accepted solution]

@v-gizhi-msft Thanks for Solutions this works 🙂 .Will try the easy way too. 👍

Is there a way we can add Total avg by each row for this? and can you suggest some blogs or material practice DAX.

[amitchandak]

@Anonymous 

Each one, you have to do like this

new measure =Averagex(SUMMARIZE(Sheet1,Sheet1[Product],"avg_RFI",SUM(Sheet1[RFI_HOURS])),[avg_RFI])

Also refer:https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

You have to first do sum then Avg.

[Anonymous]

Hi @amitchandak, I tried this earlier it didn't work. Thanks for the response 👍