Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 :
Solved! Go to 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:
Hope this helps.
Best Regards,
Giotto
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:
Hope this helps.
Best Regards,
Giotto
@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.
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:
Hope this helps.
Best Regards,
Giotto
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:
Hope this helps.
Best Regards,
Giotto
@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.
@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.
Hi @amitchandak, I tried this earlier it didn't work. Thanks for the response 👍
User | Count |
---|---|
96 | |
85 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |