Forum Discussion

richardnlove918's avatar
richardnlove918
Frequent Visitor
7 years ago
Solved

Dax Question

I have a DAX question regarding a function that is supposed to count the instances of an average value.   The image below shows correct results for the table to the left however the table to the ri...
Need Help
  • OwenAuger's avatar
    OwenAuger
    7 years ago

    Hi Richard,

     

    I am assuming your [AVGQ] measure is defined as AVERAGE ( 'Data'[qValue] ) ?

     

    I would suggest rewriting your measure like this:

    # Rows with Same Avg Value = 
VAR CurrentAverage = [AVGQ]
RETURN
COUNTROWS ( 
    FILTER (
        ALLSELECTED ( Data[Site] ),
        CurrentAverage = [AVGQ]
    )
)

    From reading your post, it looks like you want to retain the UID filter if any, and count the number of Site values (rather than the number of rows of data) for which the average qValue is the same as in the current filter context.

     

    This works in a test model I created at my end. Please post back if needed.

     

    Regards,

    Owen

OwenAuger's avatar
OwenAuger
Super User
7 years ago

Hi Richard,

 

I am assuming your [AVGQ] measure is defined as AVERAGE ( 'Data'[qValue] ) ?

 

I would suggest rewriting your measure like this:

# Rows with Same Avg Value = 
VAR CurrentAverage = [AVGQ]
RETURN
COUNTROWS ( 
    FILTER (
        ALLSELECTED ( Data[Site] ),
        CurrentAverage = [AVGQ]
    )
)

From reading your post, it looks like you want to retain the UID filter if any, and count the number of Site values (rather than the number of rows of data) for which the average qValue is the same as in the current filter context.

 

This works in a test model I created at my end. Please post back if needed.

 

Regards,

Owen

  • richardnlove918's avatar
    richardnlove918
    Frequent Visitor
    7 years ago

    Hi Owen,

     

    Thank you so much - we are almost there!

     

    When I do not filter for UID both the non-aggregated (left table) and the aggregated (right table) are correct:

     

    However when I filter for D029:

     

    or D030:

     

    The counts for Avg Value = 0.0 are incorrect.  Again for non-aggregated values we need to group counts bu UID.

     

    Thanks again for your help!

    Richard

    • OwenAuger's avatar
      OwenAuger
      Super User
      7 years ago

      Thanks for the reply Richard :)

      That's interesting - in the sample model I created, I do get the figures you're expecting when a UID slicer is included.

       

      Here's the link to my model.

      PBIX link

       

      Could you take a look and see if you can spot the difference, and feel free to post a link to your model here and I'll take a look.

       

      Just speculating - you don't have any "sort by" columns defined or any other tables whose filters might be affecting the calculation?

       

      Regards,

      Owen

      • richardnlove918's avatar
        richardnlove918
        Frequent Visitor
        7 years ago

        Hi Owen,

         

        Thanks for the quick response.

         

        It turns out I had a page level filter set for qValue is not blank.  When I remove this filter in my file I get the correct results.  When I turn it on in your file I get the incorrect results (see below).

         

        I will now explore a bit more and assuming all is well, will mark your post as the solution.  

         

        Thanks again Owen,

        Richard