DAX Average over a previous SUM

[Anonymous]

Hello guys,

I'm trying to do an Average based on a previous Sum. I'm connecting to Snowflake which doesn't have the Query Editor feature yet.

Here is the sample of data and final desired result. I'm also sharing the query that resolve it from SQL. I need the same in DAX.

Thanks in advance!

Note: the colored rows are the ones can be grouped.

Data sample and desired result.

Query that gives me the result

SQL query to get the result

[camargos88]

Hi @Anonymous ,

Try it:

Measure = AVERAGEX(SUMMARIZE(TABLE, custono, payor1, patient, cpt, "Sum", SUM(units)), Sum)

Drag the fields: ustono, payor1, patient, cpt to the table and this new measure.

[Anonymous]

camargos88  

I tried this: 

AVG_Units = AVERAGEX(SUMMARIZE(CHARGE,CHARGE[CUSTNO],CHARGE[PAYOR1],CHARGE[PATIENT],CHARGE[CPT],"SUMA",SUM(CHARGE[UNITS])),[SUMA])

and nothing. When I put the new measure to the table I got the same value as Total Units.

[camargos88]

@Anonymous ,

Can you post the data as text here, so I can take a look ?

[Anonymous]

@camargos88 

Here's a sample

ID CUSTNO PAYOR1 PATIENT CPT UNITS
89049079 CustNo1 Payor1 Patient1 CPT1 1
87987720 CustNo1 Payor1 Patient1 CPT2 4
87918407 CustNo1 Payor1 Patient1 CPT1 1
87987786 CustNo1 Payor1 Patient2 CPT1 2
87987758 CustNo1 Payor1 Patient2 CPT9 1
87987758 CustNo1 Payor1 Patient2 CPT1 6
87987786 CustNo1 Payor2 Patient7 CPT1 3
87987758 CustNo1 Payor2 Patient7 CPT9 9
87987758 CustNo1 Payor2 Patient7 CPT2 6
87987759 CustNo1 Payor2 Patient7 CPT9 12

[Anonymous]

@camargos88 

I think the SUMMARIZE part is well reflecting the subquery to get TotalUnits, what I think it's missing it's the grouping part when it does the AVERAGE. Like I do in the external query.

[camargos88]

@Anonymous ,

[Anonymous]

@camargos88 

For some reason it doesn't work for me. I'm attaching a more real data, maybe I was missing something important in the first sample. Would you mine take a look. https://www.dropbox.com/s/zdb0fqr7hnovp7g/Sample_Data.csv?dl=0 

Really appreciate your help.

[camargos88]

Hi @Anonymous ,

Are you summaring the CUSTNO and PAYOR1 columns ? Maybe it's numerical columns and when you see the values it's wrong.

Try don't aggregating them or changing them to text data type.

[Anonymous]

@camargos88 

It wasn't that, but I just confirmed that your first measure was fine!!!

THANKS A LOT!!