Distinct Count of Categories

[johmedrano]

Hello,

In Power BI I do have a visualization like this

Column from Date Table	Column from Sales Table	Column from Product Table	Column from Sales Table	Column from Sales Table
Date	Product	Category	Partner ID	Revenue
January	A1	A	X	2323
January	B1	B	Y	24342
January	B1	D	X	343443
January	C1	B	X	3434
January	B1	B	Y	34343
February	C1	C	Y	3434
February	D3	D	Y	343
February	A1	A	X	4234
February	B1	B	X	34343
February	C2	C	X	3434

And I would like to create a measure "#Average per Active Partner" that returns the average number or Categories sold per ACTIVE Partner. ACTIVE Partner =  Revenue >0

Date	# Average per Active Partner
January	1.5
February	2.5

Could you kindly advise on the DAX formula to use in this case ?

Thanks a lot for your patience and support.

[Yggdrasill · Accepted solution]

Try this measure

#ActivePartners = 
VAR _countPartnerID = DISTINCTCOUNT( SalesTable[Partner ID] )
VAR _countCategory =  DISTINCTCOUNT( SalesTable[Category] )  
RETURN
   CALCULATE( 
       DIVIDE( _countCategory ; _countPartnerID ) ; FILTER( SalesTable ; SalesTable[Revenue] > 0 )    
   )

[Vvelarde · Accepted solution]

@johmedrano

Hi, try with this measure:

Average per Partner =
VAR DistinctCategories =
    CALCULATE (
        DISTINCTCOUNT ( 'Product Table'[Category] );
        FILTER ( SalesTable; SalesTable[Revenue] > 0 )
    )
VAR ActivePartners =
    CALCULATE (
        DISTINCTCOUNT ( SalesTable[Partner ID] );
        FILTER ( SalesTable; SalesTable[Revenue] > 0 )
    )
RETURN
    DIVIDE ( DistinctCategories; ActivePartners )

Regards

Victor

Lima - Peru

[Yggdrasill]

@Vvelarde: My thinking exactly ... lol

[Yggdrasill · Accepted solution]

Try this measure

#ActivePartners = 
VAR _countPartnerID = DISTINCTCOUNT( SalesTable[Partner ID] )
VAR _countCategory =  DISTINCTCOUNT( SalesTable[Category] )  
RETURN
   CALCULATE( 
       DIVIDE( _countCategory ; _countPartnerID ) ; FILTER( SalesTable ; SalesTable[Revenue] > 0 )    
   )