measure total with multiple criteria

[Anonymous]

Hi all,

I have this measure ( SkillCount = var reportskill = SELECTEDVALUE(Sheet1[Skills])  return

COUNTROWS(

    Filter(ADDCOLUMNS(SUMMARIZE(Sheet1,Sheet[User ID],Sheet1[Skills],Sheet1[Sub-skills]),"Sum", CALCULATE(SUMX(Sheet1,'Sheet1'[% trainings]),FILTER(Sheet1,'Sheet1'[User ID] = EARLIER(Sheet1[User ID]) && 'Sheet1'[Skills] = EARLIER(Sheet1[Skills]) && 'Sheet1'[Sub-skills] = EARLIER(Sheet1[Sub-skills])

    ))),'Sheet1'[Skills] = reportskill  &&  [Sum]>=0.99)) )

for this data that gives me the number of User IDs for each skill, but Power BI does not give me the total of IDs, but I want distinctcount of the IDs because sometimes there is the same User ID that do two or more skills and for the total I only want to count the corresponding User ID once.

Can you help me please?

Thank you 

[v-lid-msft]

Hi @Anonymous ,

We can use the SUMX function in following measure to meet your requirement. Add the SUMX function outside the measure and delete the SELECTEDVALUE function.

SkillCount =

SUMX (

    VALUES ( 'Sheet1'[Skills] ),

    VAR reportskill = Sheet1[Skills]

    RETURN

        COUNTROWS (

            FILTER (

                ADDCOLUMNS (

                    SUMMARIZE ( Sheet1, Sheet1[User ID], Sheet1[Skills], Sheet1[Sub-skills] ),

                    "Sum", CALCULATE (

                        SUMX ( Sheet1, 'Sheet1'[% trainings] ),

                        FILTER (

                            Sheet1,

                            'Sheet1'[User ID] = EARLIER ( Sheet1[User ID] )

                                && 'Sheet1'[Skills] = EARLIER ( Sheet1[Skills] )

                                && 'Sheet1'[Sub-skills] = EARLIER ( Sheet1[Sub-skills] )

                        )

                   )

                ),

                'Sheet1'[Skills] = reportskill

                    && [Sum] >= 0.99

            )

        )

)

We can get the result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

[Anonymous]

Hi,

This is the sum of the User IDs that have skills but I want distinctcount of the User IDs.

For example, I have the first table but I want the second table.

Can you help me please?

Thank you

[v-lid-msft]

Hi @Anonymous ,

We can use the measure to meet your requirement.

SkillCount =
SUMX (
    VALUES ( 'Sheet1'[Skills] ),
    VAR reportskill =
        MAX ( Sheet1[Skills] )
    RETURN
        COUNTROWS (
            FILTER (
                ADDCOLUMNS (
                    SUMMARIZE ( Sheet1, Sheet1[User ID], Sheet1[Skills], Sheet1[Sub-skills] ),
                    "Sum", CALCULATE (
                        SUMX ( Sheet1, 'Sheet1'[% trainings] ),
                        FILTER (
                            Sheet1,
                            'Sheet1'[User ID] = EARLIER ( Sheet1[User ID] )
                                && 'Sheet1'[Skills] = EARLIER ( Sheet1[Skills] )
                                && 'Sheet1'[Sub-skills] = EARLIER ( Sheet1[Sub-skills] )
                        )
                    )
                ),
                'Sheet1'[Skills] = reportskill
                    && [Sum] >= 0.99
            )
        )
)

It is the same measure as before except we add MAX function in the fourth row.

Then we can get the result like this,

BTW, pbix as attached.

Best regards,

[Anonymous]

Hi,

I'm sorry but I don't want the MAX. I have an initial table with all the IDs, skills, trainings and % trainings, and I want the ID's DISTINCTCOUNT by applying the built formula, not the maximum.
Can you help me? If you need to be more explicit say it. Basically I want the ID DISTINCTCOUNT but applying the initial formula that I did and show you.

Thank you

[v-lid-msft]

Hi @Anonymous ,

Sorry for that we attached incorrect screenshot, please see following result screenshot which use measure of our previous reply, If it does not meet your requirement, could you please show more complex sample mockup data with expected result?

Best regards,

[Anonymous]

Hi @v-lid-msft ,

I added colors to file and more data to make the exercise more noticeable. Therefore, in this data, all IDs that have color have at least 1 skill. The ID 7410 has two skills but I only want to count it once, that is, I want to remove duplicates in total.

So, in these data we realize that the IDs that have at least one skill are the ones that are in color (which totals 9 IDs).
Can you help me find a formula in Power BI to get to the second table?

Thank you very much 

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can use the following measure to meet your requirement. If ID has two skills, we count it once, the total should be 8.

Measure = COUNTROWS(FILTER(DISTINCT('Sheet1'[User ID]),CALCULATE(SUM(Sheet1[% trainings])>=0.99)))

In your screenshot, the 1234 counts twice. Maybe you didn’t notice this, and marked it in two colors.

BTW, pbix as attached.

Best regards,