Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculate Average Prevalence by Group

I am having a real challenge trying to determine the DAX code to calculate the average prevalence for my filtered dataset within a category. Basically, I want to filter by a SubGroup (say "paint colors"), get the distinct count of this SubGroup and divide this total by the distinct count of the ParentGroup (say "paint"). This will get me the prevalence of the different "paint colors" within the ParentGroup.

 

This gets me the count per SubGroup:

 

 

AVERAGEX(
    KEEPFILTERS(VALUES('UNION_Industry'[Subgroup])),
    CALCULATE(DISTINCTCOUNT('UNION_Industry'[UniqueID]),'UNION_Industry'[ParentGroup] = "Filter")

 

 

 

This gets me the overall count of the ParentGroup:

 

 

CALCULATE(DISTINCTCOUNT('UNION_Industry'[UniqueID]),'UNION_Industry'[ParentGroup] = "Filter")

 

 

So how do I get the prevalence (SubGroup / ParentGroup)?

This seems like it should work, but it does not:

 

 

AVERAGEX(
    KEEPFILTERS(VALUES('UNION_Industry'[Subgroup])),
    CALCULATE(DISTINCTCOUNT('UNION_Industry'[UniqueID]),'UNION_Industry'[ParentGroup] = "Filter")
)
/
CALCULATE(DISTINCTCOUNT('UNION_Industry'[UniqueID]),'UNION_Industry'[ParentGroup] = "Filter")

 

 


Any idea how to fix this?

Need Help

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Hi Anonymous 

     

    Be aware that DAX makes calculations based on context so depending on what is the way you are calculating the values the results vary.

     

    Context can be a filter, slicer, a row in a table, other visualizations can you share a sample file or more on the context of the result you are trying to achieve, as you refer looking at the overall the formulas should work but the specifications of your model, reports and visualizations can change that.

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hi Anonymous 

     

    Do you still have issues with this?

    If you've fixed the issue on your own please kindly share your solution. If any of the above posts helped, please kindly mark it as a solution to help others find it more quickly. Thanks!

     


    Cheers,
    Sturla