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

     

     

     

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper 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 ) )