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

     

     

     

     

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
Icon for Community Champion rankCommunity 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
      Icon for Community Champion rankCommunity 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

       

       

       

       

      • smpa01's avatar
        smpa01
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Anonymous  did you try the above yet?