cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
richardnlove918 Frequent Visitor
Frequent Visitor

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

 

2019-03-20_17-16-58.png

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Dax Question

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




5 REPLIES 5
Super User
Super User

Re: Dax Question

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




richardnlove918 Frequent Visitor
Frequent Visitor

Re: Dax Question

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:2019-03-21_3-24-31.png

 

However when I filter for D029:2019-03-21_3-26-38.png

 

or D030:2019-03-21_3-27-32.png

 

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

Super User
Super User

Re: Dax Question

Thanks for the reply Richard Smiley Happy

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




richardnlove918 Frequent Visitor
Frequent Visitor

Re: Dax Question

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

2019-03-21_5-27-08.png

 

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

 

Thanks again Owen,

Richard

richardnlove918 Frequent Visitor
Frequent Visitor

Re: Dax Question

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