Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Net New Count per Batch

Hi,  I am new to PowerBI, and I am trying to find the net new count of brands per batch in my data.  Please find the below sample data.   I need to count the Net new brands in each batch, co...
  • smpa01's avatar
    smpa01
    4 years ago

    Anonymous  this is possible with the following measure

     

     

     

    Measure =
VAR _batch =
    MAX ( 'Table'[_batch] )
VAR _count =
    COUNTROWS (
        EXCEPT (
            VALUES ( 'Table'[Brand] ),
            SUMMARIZE (
                FILTER ( ALL ( 'Table' ), 'Table'[_batch] < _batch ),
                'Table'[Brand]
            )
        )
    )
RETURN
    _count

     

     

     

    You need a quanitifable batch# column

     

     

     

     

smpa01's avatar
smpa01
Community Champion
4 years ago

Anonymous  can you please provide the sample data in table format and not just picture please?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Here is the sample table: 

BrandBatch
NikeBatch1
NikeBatch2
GameBatch1
SilkBatch2
CameBatch2
AdidasBatch1
AdidasBatch2
GameBatch3
CameBatch3
hikeBatch3
dellBatch3
JoyBatch3
  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Anonymous  Thanks and what is the desired output based on this?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Desired output is the below ( As shown in the picture) :

      Batch13
      Batch22
      Batch33

       

      For Batch1 - #Net New brands is same as count of brands in Batch1, as it is the first batch

      For Batch2 - #Net New brands is 2, excluding the brands Adidas and Nike as these are available in Batch1

      For Batch3 - #Net New brands is 3, excluding the brand "Game" which is available in Batch1 and "Came" which is in Batch2

      So the count that we project in the summary needs to be count of brands that are not available in any of the previous batches

      • smpa01's avatar
        smpa01
        Community Champion
        4 years ago

        Anonymous  this is possible with the following measure

         

         

         

        Measure =
VAR _batch =
    MAX ( 'Table'[_batch] )
VAR _count =
    COUNTROWS (
        EXCEPT (
            VALUES ( 'Table'[Brand] ),
            SUMMARIZE (
                FILTER ( ALL ( 'Table' ), 'Table'[_batch] < _batch ),
                'Table'[Brand]
            )
        )
    )
RETURN
    _count

         

         

         

        You need a quanitifable batch# column