Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 👍
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |