Forum Discussion
Monthly averages - Calculated column - Aggregation
Suppose I have the following table:
| Date | Product | STD |
| 2025-02-05 | XX | 1 |
| 2025-02-05 | XX | 1 |
| 2025-02-06 | XX | 0 |
| 2025-02-08 | XX | 0 |
| 2025-02-10 | YY | 0 |
| 2025-03-02 | XX | 1 |
| 2025-03-04 | YY | 0 |
| 2025-03-07 | XX | 0 |
Date is a calculated table based on max / min from a collection of dates from different sources. Sort of a master date table, which is then linked to the different tables. Date has a date hierarchy (Year, Month, Day, Quarter).
Product is a calculated column which determines the product type.
STD is a calculated column that determines if product XX (products of other type, e.g., YY, is kind of irrelevant) was made using standard procedures: 1 if it is, 0 if not. Could have been of Boolean type. Does not really matter.
What I want to get is monthly averages in a table visual, like so:
| Date (Year & Month) | Total Products of Type XX | # of STD Products XX | % of STD Products XX |
| 2025-02 | 4 | 2 | 50% |
| 2025-03 | 2 | 1 | 50% |
Getting the total products of type XX and the # of STD Products XX is easy. Getting the % of STD Products XX is what I'm struggling.
I know that I'm struggling with the aggregation. The calculated column, which I'm using for calculating the percentage, works row by row, whereas I need to calculate, for every date on the month, the montlhy average and then on the visual choose not to summarize.
Any help is appreciated! Copilot is not useful, and Google was not my friend.
Hi Cro-Magnon,
To fix this, you can use an below measure that calculates the percentage correctly by summing up the values at the level you want.
Dax Measure :STD_Percentage_XX = DIVIDE([TotalProducts_XX], [STD_Products_XX], 0 )
the expected output as below :
This measure works with the aggregated totals instead of individual row context, providing the correct percentage of standard products.If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.
If you continue to face issues, feel free to reach out to us for further assistance!
6 Replies
Hello,
I am not quite sure what exactly you are trying to calculate - Average by date, or Average by month? In either case, you could consider the following:Measure = VAR _Percent = DIVIDE([STD Products],[Total Products],0) RETURN CALCULATE(_Percent,ALLEXCEPT(DateTable,DateTable[Month])Hi,
that does not work because of the step:VAR _Percent = DIVIDE([STD Products],[Total Products],0)
is a row by row calculation and therefore, for any given row, it will always be 1 if the product is standard. That is exact issue I'm struggling with. You can test it with the mockup table in my original post.Hi Cro-Magnon,
To fix this, you can use an below measure that calculates the percentage correctly by summing up the values at the level you want.
Dax Measure :STD_Percentage_XX = DIVIDE([TotalProducts_XX], [STD_Products_XX], 0 )
the expected output as below :
This measure works with the aggregated totals instead of individual row context, providing the correct percentage of standard products.If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.
If you continue to face issues, feel free to reach out to us for further assistance!
