Help with DAX formula

[rit_ty7]

Hello everyone,
I want the below formula defined in a measure to group by month, but the dax'es I've tried is unable to group it by month.

Hit_Rate%_15th =

DIVIDE(COUNTROWS(FILTER('Placement S&OP Nov', 'Placement S&OP Nov'[Hit by 15th] = 1)), COUNTROWS('Placement S&OP Nov'), 0)

Any help will be appreciated

[rit_ty7]

More context:
Earlier, this measure was calculating data only for the November table, which contained 7k rows. After appending the October and December tables, the total row count increased to 19k. Now, I want the measure to calculate the hit rate specifically for October when October is selected in the slicer, and similarly for any other selected month, i.e, modify the above code so that it filters month effectively.

[Anonymous]

Hi, @rit_ty7 

Based on your information, I create a sample table:

Then create a new measure and try the following DAX:

Hit_Rate%_15th_Monthly = 
CALCULATE(
    DIVIDE(
        COUNTROWS(FILTER('Table', 'Table'[Hit by 15th] = 1)), 
        COUNTROWS('Table'), 
        0
    ),
    ALLEXCEPT('Table', 'Table'[Month])
)

Create a slicer visual, put Month in slicer visual. Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @rit_ty7  - Since you are now working with 3 months of data and the existing formula is not filtering correctly

Hope you already have a  proper date table in your model, you can use it to simplify the filtering

Hit_Rate%_15th =
DIVIDE(
CALCULATE(
COUNTROWS('Placement S&OP Nov'),
'Placement S&OP Nov'[Hit by 15th] = 1
),
COUNTROWS('Placement S&OP Nov'),
0
)

When using this, make sure the Year and Month fields from your date table are included in the visual, and the relationships between the date table and your fact table are correctly configured.

[danextian]

Hi @rit_ty7 

Please provide a workable sample data (not an image), your expected result from that and the reasoning behind. Please refer to this post. https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447... 

[rit_ty7]

I'm unable to share sample data as the data is huge, previously we were calculating hit_rate for 1 month so that was not a problem but now we're working with 3 month's data and the formula is not filtering correctly.

[danextian]

A sample data doesnt have to be the actual data but a representation of what the actual is. The link in  my initial reply should have a guide.