cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
svalen Member
Member

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:

 

Untitled.png

 

 

 

 

 

 

 

 

 

 

 

 

Any ideas?

 

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

 

Thanks in advance! Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Count over Measure with Conditions

Hi @svalen,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

11 REPLIES 11
Super User
Super User

Re: Count over Measure with Conditions

Hi @svalen,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




svalen Member
Member

Re: Count over Measure with Conditions

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

Super User
Super User

Re: Count over Measure with Conditions

@svalen

 

Try this one

 

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

Re: Count over Measure with Conditions

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

Super User
Super User

Re: Count over Measure with Conditions

Can you share you PBIX with me?



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




svalen Member
Member

Re: Count over Measure with Conditions

@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 )
svalen Member
Member

Re: Count over Measure with Conditions

Claro!

Super User
Super User

Re: Count over Measure with Conditions

send it trough private message 😄



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Community Support Team
Community Support Team

Re: Count over Measure with Conditions

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

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 371 members 3,429 guests
Please welcome our newest community members: