Average of Monthly Distinct Headcounts

[johmedrano]

Hello,

I have a visualization like this in Power BI Desktop:

I woud need to create a MEASURE that gives me the Average Number of Unique Categories sold by Partners in each month.  It woud be shown like this:

1.5 = (Unique Categories Sold by Partner "X" (2) + Unique Categories Sold by Partner "Y" (1) / Number of Partners (2) 

I would be very thankful if someone could hel me here.  

Joaquin

[Zubair_Muhammad · Accepted solution]

Hi @johmedrano

Try this MEASURE

Measure =
SUMX (
    VALUES ( SalesTable[Partner ID] ),
    CALCULATE ( DISTINCTCOUNT ( SalesTable[Category] ) )
)
    / DISTINCTCOUNT ( SalesTable[Partner ID] )

[johmedrano]

Hello and thank you for your help here.

Could you kindly advise on the formula to use in case that the relationship between Products and Categories is not in the Sales Table but in a separate table.  In the Sales table we would only see Products.  Thanks !

[johmedrano]

Thanks a lot for your answer.

Your formula is giving me the result below (see table).

I would need that DistinctCount of Partners ID is based on each specific month and not on the entire Sales Table. 

Do you possibly know how I can add that to your DAX formula ?

Sorry for my lack of knowledge here... I am really new in DAX.

 

[Zubair_Muhammad]

@johmedrano

Is the issue resolved?

[johmedrano]

Yes, the issue is solved. I used this formula:

Measure =
SUMX (
    VALUES ( SalesTable[Partner ID] ),
    CALCULATE ( DISTINCTCOUNT ( SalesTable[Category] ) )
)
    / CALCULATE(DISTINCTCOUNT(SalesTable[Partner ID]),Filter(Sales,[Total Sales]>0))