Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Count over Measure with Conditions

Hi guys.

 

I am trying to get a Count over a measure with specific conditions, like value between 20 and 25% and value over 25%.

 

I honestly have no clue on how to do it. I built this table to picture what I am looking for, I think it's clearer this way:

 

 

 

 

 

 

 

 

 

 

 

 

 

Any ideas?

 

I don't need all the code, maybe with a few hints I can do it.

 

Thanks in advance! :smileyhappy:

Need Help
Show and Tell
  • MFelix's avatar
    MFelix
    8 years ago

    Hi Anonymous,

     

    Based on the PBIX file you shared I believe that the  issue is based on context because in the measure you created you are making based on Name and Date but on the count of the percentages you are not giving that same context so the result is not correct.

     

    Create a column based on the date from your table:

     

    MONTH = FORMAT(TABLE[DATE];"mm-yyyy")

    Then create the following measure:

     

    COUNT % 20 - 25 =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            ALL ( TABLE[NAME]; TABLE[MONTH] );
            TABLE[NAME];
            TABLE[MONTH];
            "% Count"; [MEASURE TO BE COUNTED]
        );
        [MEASURE TO BE COUNTED] >= 0,2
            && [MEASURE TO BE COUNTED] <= 0,25
    )
)

     

     

    Believe this should work, but check the PBIX file I have return to you trough private message.

     

     

    Regards,

    MFelix

12 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    Hi Anonymous,

     

    You need to make a aggregate you measure within a summarize table.

     

    Try something like this:

     

    COUNT MEASURE =
CALCULATE (
    COUNTROWS (
        FILTER (
            SUMMARIZE ( ALL ( Table1[Type]; Table1[Type] ); Table1[Type]; "MEASURE TO COUNT"; [MEASURE] );
            [MEASURE TO COUNT] < 0,25
                && [MEASURE TO COUNT] > 0,2
        )
    )
)

    Adjust the coding to what you need.

     

    Regards,

    MFelix

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      MFelix, the Great:

       

      I tried your code and it didn't work, it returns the same value for all the Types. I also tried the alternative on comment below.

       

      XXX = 
CALCULATE (
    COUNTROWS (
        FILTER (
            SUMMARIZE ( ALL ( ''Table1'[Type]; ''Table1'[Type] ); ''Table1'[Type]; "MEASURE TO COUNT"; [MEASURE] );
            [MEASURE TO COUNT] <= 0,25
                && [MEASURE TO COUNT] >= 0,2
        )
    )
)

/*
XXX = 
CALCULATE (
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                SUMMARIZE (
                    ALL ( ''Table1'[Type] );
                    ''Table1'[Type]
                );
                "MEASURE TO COUNT"; [MEASURE]
            );
            [MEASURE TO COUNT] >= 0,2
                && [MEASURE TO COUNT] <= 0,25
        )
    )
)
*/

      Any thoughts?

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Anonymous

         

        Try this one

         

        Measure =
COUNTROWS (
    FILTER (
        VALUES ( Table1[Name] ),
        [MEASURE TO COUNT] < 0.25
            && [MEASURE TO COUNT] > 0.2
    )
)
  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    8 years ago

    Hi svalen ,

     

    "I am trying to get a Count over a measure with specific conditions, like value between 20 and 25% and value over 25%."

    <--- What's your expected result? Could you give some example? In addtion, Could you share a mockup with some dummy data for further analysis?

     

     

    Regards,

    Jimmy Tao

     

  • ramzan11b's avatar
    ramzan11b
    Regular Visitor
    3 years ago

    Awsome and Fantastic.
    Thanks very much. I was facing a problem in such a measure. This Post solved my probem.🙂
    👍👍