Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Distinctcount Companies with no specific purchase

Hi all,

 

I have a Sales Table with 

 

Company ID | Job Type | Revenue | Order Date 

 

Company IDJob TypeRevenueOrder Date
1Beer10jan-20
1Cookies5jan-20
2Beer10jan-20
2Cookies5jan-20
3Cookies5jan-20
3Beer10jan-20
3Cookies5jan-20
4Beer5jan-20
5Beer5feb-20

 

I need to discinctcount the number of Companies that has NOT bought Cookies, split on Month. Meaning, for January 2020 they can not have ordered Cookies at all. For this example Company 4 and 5 have not ordered Cookies, while the rest have. Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

     

    Hi Anonymous ,

    You can create two measures as below:

    Measure = 
VAR _curdate =
    MAX ( 'Sales'[Order Date] )
VAR _a =
    CALCULATE (
        MAX ( 'Sales'[Job Type] ),
        FILTER (
            ALLSELECTED ( 'Sales' ),
            'Sales'[Company ID] = MAX ( 'Sales'[Company ID] )
                && 'Sales'[Order Date] = MAX ( Sales[Order Date] )
                && 'Sales'[Job Type] = "Cookies"
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Sales'[Company ID] ),
        FILTER ( 'Sales', ISBLANK ( _a ) )
    )
    Measure 2 = 
SUMX (
    VALUES ( 'Sales'[Company ID] ),
    SUMX ( VALUES ( 'Sales'[Order Date] ), [Measure] )
)

    Best Regards

    Rena

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Try this with Jan 20 as a filter in the slicer

     

    Measure =if(sum(Table[Revenue])+0 =0, blank(), 1)

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak 

       

      I need to calculate the number of Companies NOT ordering Cookies.

       

      My problem is that they are multiple times in my Sales table, and my Power BI counts them row by row. I need to calculate, per month, that not a single row included "cookies".

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

         

        Hi Anonymous ,

        You can create two measures as below:

        Measure = 
VAR _curdate =
    MAX ( 'Sales'[Order Date] )
VAR _a =
    CALCULATE (
        MAX ( 'Sales'[Job Type] ),
        FILTER (
            ALLSELECTED ( 'Sales' ),
            'Sales'[Company ID] = MAX ( 'Sales'[Company ID] )
                && 'Sales'[Order Date] = MAX ( Sales[Order Date] )
                && 'Sales'[Job Type] = "Cookies"
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Sales'[Company ID] ),
        FILTER ( 'Sales', ISBLANK ( _a ) )
    )
        Measure 2 = 
SUMX (
    VALUES ( 'Sales'[Company ID] ),
    SUMX ( VALUES ( 'Sales'[Order Date] ), [Measure] )
)

        Best Regards

        Rena