Counting the rows where the value is greater than a benchmark

[Chris_23]

Hi,

I want to get the number of rows where the offer of a selectedvalue is higher compared to the benchmark "Fozzie Bear".

PBIX:

https://1drv.ms/u/s!AsLM3f2rQCP34xv34CARLr8vZ0bv?e=NNzvVi

For example, if I select "Rizzo the Rat", then Count = 1, since
Project MPS: Fozzie Bear 433,015 > Rizzo the Rat 316,426
Project Disney World: Rizzo the Rat 449,034 > Fozzie Bear 256,240

However, the Measure Count shows

Count = Calculate(COUNT('Table'[Job]),Filter('Table',calculate(MAX('Table'[Offer]),'Table'[Vendor]="Fozzie Bär") < calculate(MAX('Table'[Offer]),'Table'[Vendor]=selectedvalue('Table (2)'[Vendor]))))

= 2

Any idea what to improve?

Kind regards

Chris

[Chris_23]

Hi @v-jianboli-msft ,

thanks a lot.

[v-jianboli-msft · Accepted solution]

Hi @Chris_23 ,

Please try:

Count =
VAR _a =
    SUMMARIZE (
        'Table',
        'Table'[Job],
        'Table'[Vendor],
        "Flag",
            IF (
                SUMX (
                    FILTER (
                        ALL ( 'Table' ),
                        [Job] = EARLIER ( 'Table'[Job] )
                            && [Vendor] = "Fozzie Bär"
                    ),
                    [Offer]
                )
                    < SUM ( 'Table'[Offer] ),
                1
            )
    )
RETURN
    IF ( ISFILTERED ( 'Table (2)'[Vendor] ), SUMX ( _a, [Flag] ) )

Output:

Best Regards,

Jianbo Li

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