Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I need your help.
I'm developping a matrix report and I need to create a measure for the average of a Category of Product.
I have a table like this:
Date | category | product | weight | count |
January 2019 | CatA | Product1 | 1 | 5 |
January 2019 | CatA | Product2 | 5 | 3 |
January 2019 | CatB | Product3 | 10 | 3 |
January 2019 | CatC | Product4 | 1 | 10 |
And I need to have a report to display the Average of the Category like this:
Date | category | product | weight | count | Average |
January 2019 | CatA | Product1 | 1 | 5 | 2,5 |
January 2019 | CatA | Product2 | 5 | 3 | 2,5 |
January 2019 | CatB | Product3 | 10 | 3 | 3,07 |
January 2019 | CatB | Product4 | 1 | 10 | 3,07 |
Please Can you help us to create this Average Measure ?
The formula is : Sum (weight * Count By Category ) / Sum (Count By Category)
Thank you for your help
Solved! Go to Solution.
Hi @Anonymous
You may try below measure:
Measure = SUMX(FILTER(ALL(Table1),Table1[category]=MAX(Table1[category])),Table1[weight]*Table1[count])/CALCULATE(SUM(Table1[count]),ALLEXCEPT(Table1,Table1[category]))
Regards,
Hi @Anonymous
You may try below measure:
Measure = SUMX(FILTER(ALL(Table1),Table1[category]=MAX(Table1[category])),Table1[weight]*Table1[count])/CALCULATE(SUM(Table1[count]),ALLEXCEPT(Table1,Table1[category]))
Regards,
Hi @Anonymous ,
I think this should help:
MEASURE = CALCULATE( Sum (weight * Count ) / Sum (Count) , ALLEXCEPT( TableName , Category ) )
Note: If you have multiple Months in this table I would probably add MONTH as well within the ALLEXEPT function.
Let me know if that helps.
Cheers,
Robin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |