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

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

richardnlove918's avatar
richardnlove918
Frequent Visitor
7 years ago

Hi Owen,

 

I've explored the data, made minimum adjustments on my end to account for blanks, etc and now have a function that forms the basis for replicating the percentrank.inc function in Excel in a way that can be sliced and diced - I can't thank you enough!

 

Best,

Richard