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, comparing the previous batches. I would also get more batches like batch4, batch 5 in future. 

Desired output:

 

I am unsure on how to bring in the "not in the previous batch" condition to compute for every batch. Can anyone help me with this please? 

 

Thanks in Advance,

Anu

  • 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

     

     

     

     

7 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    One of many ways to solve this is to have a Batch_dimenstion table with the index number column that can identify which batches are the previous ones.

     

     

    Brand count not in the previous batches: =
    VAR currentbatchindex =
    MAX ( 'Batch'[Index] )
    VAR currentbatchbrand =
    VALUES ( Data[Brand] )
    VAR previousbatchesbrand =
    CALCULATETABLE (
    VALUES ( Data[Brand] ),
    FILTER ( ALL ( 'Batch' ), 'Batch'[Index] < currentbatchindex )
    )
    VAR onlycurrentbatchbrand =
    EXCEPT ( currentbatchbrand, previousbatchesbrand )
    RETURN
    IF ( HASONEVALUE ( 'Batch'[Batch] ), COUNTROWS ( onlycurrentbatchbrand ) )
     
  • 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?