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 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!!

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.

2 Replies

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.