Forum Discussion

Migsmix's avatar
Migsmix
Frequent Visitor
4 years ago
Solved

Count & Distinctcount Dax Formula

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 rep...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
lbendlin
Super User
4 years ago

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.