Count & Distinctcount Dax Formula

[Migsmix]

Hi,

I need help with some visuals and DAX i'm trying to make. Currently i have a report in excel but now that has a lot of data it's hard to use. In the attached excel file you can see how my report works  File example . The PBI should look like the tab that says "Final in PBI". It has all the formulas. I was trying some Count Dax but it doesn't work. 

Thanks!!

[Anonymous · Accepted solution]

Hi @Migsmix ,

Please refer to my pbix file to see if it helps you.

Create three measures.

distribution =
CALCULATE (
    COUNT ( Database[Code 2] ),
    FILTER (
        ALL ( Database ),
        Database[Cluster] = SELECTEDVALUE ( Database[Cluster] )
            && Database[Code 2] = SELECTEDVALUE ( Database[Code 2] )
            && Database[Brand] = SELECTEDVALUE ( Database[Brand] )
    )
)

number of stores =
CALCULATE (
    DISTINCTCOUNT ( Database[Store Name] ),
    FILTER (
        ALL ( Database ),
        Database[Cluster] = SELECTEDVALUE ( Database[Cluster] )
    )
)

mea % = [distribution]/[number of stores]

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Thank you for providing the sample data. That helps a lot with proposing a potential solution.  However your expected result does not match your sample data.

Please show the expected result based on the sample data.  See attached for a starting point.