Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Countifs result from measures

Hello everyone,

 

This task would normally be very easy to accomplish on excel, but I have having trouble with DAX.

 

I have a table which have multiple columns.

I have created a measure to find the KPI by using the Divide function on dax, so my KPI is a measure.

 

Now I have a table that has those KPI by month for the curent fiscal year and by location:
The value that you see is based on :

KPI = Divide(
    SUM(Table1[Column1]),SUM(Table1[Column2]),BLANK())

 

All I am trying to do is count rows when the KPI is lesser than .80. 

So row 1 - 4 will show 0 (as all met the KPI), but row #5 will show 1 because KPI for that month July is 0.66. I want a total count for the rows in this year. On excel I would have just done "=COUNTIF(A1:M1,"<0.8")..  Any guidance would grealy be appreciated.

 

Regards,

 

 

 

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi Anonymous ,

     

    You may create a measure like this:

    Below = 
COUNTROWS(
    FILTER(
        SUMMARIZE (
        'table',
        'Table'[Product],
        'Table'[Month],
        "Kpi", 
            [Kpi]
         ),
    [Kpi] < 0.96
    ) 
)

     

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

1 Reply

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    You may create a measure like this:

    Below = 
COUNTROWS(
    FILTER(
        SUMMARIZE (
        'table',
        'Table'[Product],
        'Table'[Month],
        "Kpi", 
            [Kpi]
         ),
    [Kpi] < 0.96
    ) 
)

     

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