Count over Measure with Conditions

[Anonymous]

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!

[ramzan11b]

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

[v-yuta-msft]

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

[MFelix]

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]

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

@Anonymous

Try this one

Measure =
COUNTROWS (
    FILTER (
        VALUES ( Table1[Name] ),
        [MEASURE TO COUNT] < 0.25
            && [MEASURE TO COUNT] > 0.2
    )
)

[Anonymous]

@Zubair_Muhammad

Thanks for the suggestion, but no luck still, it returns 1 for every Type.

I also tried like:

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

We are missing something here...

[MFelix · Accepted solution]

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

[Anonymous]

@MFelix@v-yuta-msft@Zubair_Muhammad

Thanks everyone for the contribution, the issue is now solved, @MFelix solved it perfectly!

Cheers!

[Anonymous]

@MFelixand @Zubair_Muhammad

BTW, the existing measure is something like this:

MEASURE =
VAR X1 =
    CALCULATE ( [SO UNITS]; 'DimP'[COD] = 111 )
        + CALCULATE ( [SO UNITS]; 'DimP'[COD] = 222 )
VAR X2 =
    CALCULATE ( [SO UNITS]; 'DimP'[COD2] = 333 )
        + CALCULATE ( [SO UNITS]; 'DimP'[COD2] = 444 )
RETURN
    DIVIDE ( X1; X2 )

[MFelix]

Can you share you PBIX with me?

[Anonymous]

Claro!

[MFelix]

send it trough private message 😄