count value measure based on different criterias

[Anonymous]

Hi,

I need help to create a measure for a tile. I have not been able myself to create it.

The table below is used to give an overview if customer A has bought the same items as customers B (Holding Names).

The columns sold and quantity is for each item. The Incoming Order indicates if customer A has bought the same item as customers B. The Count of Holding Names indicates how many of the selected customers B has bought the item.

In this case customers B contains 5 Holding Names.

I want the tile to show me how many items has customer A not bought ("0"), which all of customers B has bought. In the table below the tile should say "1".

Also, the numbers of holding names in customers B depends on how many the user select froma filter.

Hope it makes sense otherwise I will try to elaborate.

Thanks in advance!

[v-lid-msft]

Hi @Anonymous ,

We can try to use the following measure to meet your requirement:

Measure =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            'Items',
            'Items'[ID],
            "Incoming Order Value", [Incoming Order],
            "Holding Count", DISTINCTCOUNT ( 'Items'[Holder] )
        ),
        [Incoming Order Value] = 0
            && [Holding Count] = CALCULATE ( DISTINCTCOUNT ( 'Items'[Holder] ) )
    )
)

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best regards,

[Anonymous · Accepted solution]

Hi  v-lid-msft,

Thanks for looking into it. I tried your measure and it looks like being somehow close to what I want.

The data is confidential, but I have tried to create some small dummy data in an Excel file together with explanations and a screenshot of the report.

Hope it helps the understanding. 

OneDrive link is here.

https://1drv.ms/x/s!AnNqgZEe-Ce5gRTl_9QbpDe0woKd?e=1WxWbk