Dax Question

[richardnlove918]

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 right is displaying incorrect results - I've added the expected results in red.

In the table to the left, for each UID the function is counting the instances within a UID for each average value.

I am looking to do the same thing for averages aggregated across both UIDs as shown on the right.

Here is the code for the sample data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjEwslTSUTIE4uDMklQDQ6VYHbioKUzUBFnUACZqiiwKN8EMKmpsgM1csKgBurmoak2xqgWaGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [UID = _t, qValue = _t, Site = _t])
in
    Source

Here is the code for the function which is returning incorrect results for aggregated data:

# Rows with Same Avg Value = 
CALCULATE (
    COUNTROWS (Data),
    FILTER (
        ALLSELECTED (Data),
        [AVGQ] = AVERAGE ( 'Data'[qValue])
        && Data[UID] = MAX ( Data[UID] )
    
    )
)

Thank you for your support!

Best,

Richard

[OwenAuger · Accepted solution]

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]

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]

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]

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]

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