Forum Discussion
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...
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 _countYou need a quanitifable batch# column
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
Community Champion
Community ChampionAnonymous 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?