Count on a measure

[Syndicate_Admin]

Good afternoon.

I have created a measure (Historical Q V/H), whose results are Q1,Q2,Q3 and Q4.

I need to know how many values of each Q there are in the measure.

Q4 Count = COUNTROWS(FILTER(VALUES('Times Integrated Marker Advisor'[Agent]),'Times Integrated Scoreboard Advisor'[Historical Q V/H]="Q4"))

I have created the above measure but the results are not consistent.

How could I do it?

Thanks a lot.

[Syndicate_Admin]

Good afternoon.

The result he shows me is the same, but it is not reality.

The table is as follows:

And I create 7 measures for each value in the Level 1 field:

An example:

Historical Q V/H = IF(and(SELECTEDVALUE('Integrated Scoreboard Times Advisor'[Level 1],'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor])="Historical",'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]<0.07),"Q4",IF(and(or(SELECTEDVALUE('Integrated Scoreboard Times Advisor'[Level 1],'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor])="Historical",'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]>=0.07),'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]<0.1),"Q3",IF(and(or(SELECTEDVALUE('Integrated Scoreboard Times Advisor'[Level 1],'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor])="Historical",'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]>=0.1),'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]<0.15),"Q2",IF(and(SELECTEDVALUE('Integrated Scoreboard Times Advisor'[Level 1],'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor])="Historical",'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]>=0.15),"Q1",""))))

Now I want to make a count of each of the 7 measurements, whose values are always Q1,Q2,Q3 and Q4.

I will have to do a total of 28 counting measures (7x4) to be able to know how many advisors there are with the Q1 of Level 1 = Historica for example.

Thanks a lot.

[amitchandak]

@Syndicate_Admin , Try like

Q4 Count = Countx(FILTER(VALUES('Times Integrated Marker Advisor'[Agent]),'Times Integrated Scoreboard Advisor'[Historical Q V/H]="Q4"),[Agent])

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Syndicate_Admin]

Good afternoon.

The result he shows me is the same, but it is not reality.

The table is as follows:

And I create 7 measures for each value in the Level 1 field:

An example:

Historical Q V/H = IF(and(SELECTEDVALUE('Integrated Scoreboard Times Advisor'[Level 1],'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor])="Historical",'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]<0.07),"Q4",IF(and(or(SELECTEDVALUE('Integrated Scoreboard Times Advisor'[Level 1],'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor])="Historical",'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]>=0.07),'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]<0.1),"Q3",IF(and(or(SELECTEDVALUE('Integrated Scoreboard Times Advisor'[Level 1],'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor])="Historical",'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]>=0.1),'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]<0.15),"Q2",IF(and(SELECTEDVALUE('Integrated Scoreboard Times Advisor'[Level 1],'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor])="Historical",'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]>=0.15),"Q1",""))))

Now I want to make a count of each of the 7 measurements, whose values are always Q1,Q2,Q3 and Q4.

I will have to do a total of 28 counting measures (7x4) to be able to know how many advisors there are with the Q1 of Level 1 = Historica for example.

Thanks a lot.

[v-rzhou-msft · Accepted solution]

Hi @Syndicate_Admin,

I suggest you to add [Historical Q V/H] measure as a column in a virtule table. And then count the Agent by filter [Historical Q V/H] in that table.

Q4 Count =
VAR _Historical_Q_VH =
    ADDCOLUMNS (
        ALL ( 'Times Integrated Marker Advisor' ),
        "Historical Q V/H",
            IF (
                AND (
                    SELECTEDVALUE (
                        'Integrated Scoreboard Times Advisor'[Level 1],
                        'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]
                    ) = "Historical",
                    'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor] < 0.07
                ),
                "Q4",
                IF (
                    AND (
                        OR (
                            SELECTEDVALUE (
                                'Integrated Scoreboard Times Advisor'[Level 1],
                                'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]
                            ) = "Historical",
                            'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor] >= 0.07
                        ),
                        'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor] < 0.1
                    ),
                    "Q3",
                    IF (
                        AND (
                            OR (
                                SELECTEDVALUE (
                                    'Integrated Scoreboard Times Advisor'[Level 1],
                                    'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]
                                ) = "Historical",
                                'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor] >= 0.1
                            ),
                            'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor] < 0.15
                        ),
                        "Q2",
                        IF (
                            AND (
                                SELECTEDVALUE (
                                    'Integrated Scoreboard Times Advisor'[Level 1],
                                    'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]
                                ) = "Historical",
                                'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor] >= 0.15
                            ),
                            "Q1",
                            ""
                        )
                    )
                )
            )
    )
RETURN
    COUNTX ( FILTER ( _Historical_Q_VH, [Historical Q V/H] = "Q4" ), [Agent] )

Best Regards,
Rico Zhou

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