Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BriefStop
Frequent Visitor

How to calculate on grouped by values in a single DAX measure?

I have a list of IDs and timestamps. I need to do a distinct count of IDs by hour and then average each hour's count in a measure. I'd prefer not to create separate tables because I need to do this for several indicator cars using different columns so I want to be able to do the group by inside a measure.

 

Source Data

HourID
8:00 AMA
8:00 AMA
9:00 AMA
9:00 AMD
9:00 AME
10:00 AMA
10:00 AMG
10:00 AMH
10:00 AMI
10:00 AMJ
11:00 AMK
11:00 AML
12:00 PMM

 

Inbetween Table if I had to set up a separate table to do the calculation

HourDistinct Count of ID
8:00 AM1
9:00 AM3
10:00 AM5
11:00 AM3

 

Final Indicator Card Value that I want to create

Average of Distinct Count of ID3

 

Is there a way for me to do this without having to create the inbetween table? My final visual is an indicator card so I can't use a table to group by the measure

1 ACCEPTED SOLUTION
Ritaf1983
Super User
Super User

Hi @BriefStop 
You can achieve your goal with 2 simple measures:
1. For the distinct count of IDS by Hour:

DistinctID = if(HASONEVALUE('Table'[Hour]),AVERAGEX(values('Table'[Hour]),DISTINCTCOUNT('Table'[ID])),SUMX(values('Table'[Hour]),DISTINCTCOUNT('Table'[ID])))
Ritaf1983_0-1685764492358.png

2. For Fixing "wrong" total :

AverageTotal =
VAR AVERAGE_ =
    AVERAGEX ( ALL ( 'Table'[Hour] ), [DistinctID] )
RETURN
    ( IF ( HASONEVALUE ( 'Table'[Hour] ), [DistinctID], AVERAGE_ ) )
 
Ritaf1983_1-1685764619714.png

 

Link to the sample file 

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

These measures work

ID count = DISTINCTCOUNT(Data[ID])
Measure = AVERAGEX(VALUES(Data[Hour]),[ID count])

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hey @Ashish_Mathur ,

can you please explain the advantage of your solution over mine. Your DAX contains more or less the same code except that is split across two measures, instead of using only one.

I'm always eager to learn and improve.

 

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

You are correct.  My post indeed does not add any value at all.  Sorry about the duplicate post.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ritaf1983
Super User
Super User

Hi @BriefStop 
You can achieve your goal with 2 simple measures:
1. For the distinct count of IDS by Hour:

DistinctID = if(HASONEVALUE('Table'[Hour]),AVERAGEX(values('Table'[Hour]),DISTINCTCOUNT('Table'[ID])),SUMX(values('Table'[Hour]),DISTINCTCOUNT('Table'[ID])))
Ritaf1983_0-1685764492358.png

2. For Fixing "wrong" total :

AverageTotal =
VAR AVERAGE_ =
    AVERAGEX ( ALL ( 'Table'[Hour] ), [DistinctID] )
RETURN
    ( IF ( HASONEVALUE ( 'Table'[Hour] ), [DistinctID], AVERAGE_ ) )
 
Ritaf1983_1-1685764619714.png

 

Link to the sample file 

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

TomMartens
Super User
Super User

Hey @BriefStop ,

 

this measure helps to create what you want:

Measure = 
AVERAGEX(
    VALUES( 'Table'[Hour] )
    , CALCULATE( DISTINCTCOUNT('Table'[ID] ) )
)

But I'm not sure about the expected result, because I get 2.4 instead of the expected 3:
image.png
The measure returns the same result in the Total line (no filter context does it's magic here) and the Card visual.
The measure iterates across a table of hours and determines the average by dividing the numeric expression . by the number of rows. You have to be aware that the number of rows is 1 inside the table visual but five at the total line.

 

Hopefully, this provides what your are looking for to tackle your system.

 

Regards,

Tom 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.