Monthly averages - Calculated column - Aggregation

[Cro-Magnon]

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.

[ExcelMonke]

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])

[Cro-Magnon]

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.

[v-aatheeque · Accepted solution]

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!

[v-aatheeque]

Hi @Cro-Magnon ,

If our response addressed your query, please mark it as Accepted Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[v-aatheeque]

Hi @Cro-Magnon ,

Has your issue been resolved? If a community member's response addressed your query, please consider marking it as Accepted Answer and click Yes if you found it helpful.

If you have any further questions, feel free to reach out.
Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-aatheeque]

Hi @Cro-Magnon ,

If our response addressed your query, please mark it as Accepted Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!