Average on distinctCount

[Anonymous]

Dear everyone,

I need help to have the average of a distinctCount. I did some research , but without success. I hope that I will be clear:

I have the data below:

I would like to know the distinct count color per country per product, so I created a new measure DistinctCount. So I created a matrix that display these info:

But now, I need the average per country in a diagram or table like below in blue. I don't know how to do it. Can you please help me ? Thank you very very much !

[VahidDM · Accepted solution]

Hi @Anonymous 

Try this measure:

Average Per Country = 
VAR _A =
    SUMMARIZE (
        'Table',
        'Table'[Country],
        'Table'[Product],
        "DisC", DISTINCTCOUNT ( 'Table'[Color] )
    )
RETURN
    AVERAGEX ( _A, [DisC] )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

 Do you like to see the FIFA Word Cup Dashboard:

https://community.powerbi.com/t5/Charticulator-Design-Challenge/FIFA-World-Cup-Medal-Records/cns-p/2545975

[Anonymous]

Thank you very very much @VahidDM !!! It works very well ! You saved my day 😁