Net New Count per Batch

[Anonymous]

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

[Jihwan_Kim]

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]

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

[Anonymous]

Here is the sample table: 

Brand	Batch
Nike	Batch1
Nike	Batch2
Game	Batch1
Silk	Batch2
Came	Batch2
Adidas	Batch1
Adidas	Batch2
Game	Batch3
Came	Batch3
hike	Batch3
dell	Batch3
Joy	Batch3

[smpa01]

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

[Anonymous]

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

Batch1	3
Batch2	2
Batch3	3

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 · Accepted solution]

@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]

@Anonymous  did you try the above yet?