topic Re: help with my Coalesce in DAX Commands and Tips

help with my Coalesce

Anonymous — Wed, 24 Aug 2022 09:23:38 GMT

Hi,
I hope someone can help. I am stuck.
Below are two different DAX functions to calculate my averages.
The first one (Average Coalesce function) gives me the precise results with full data e.g. Q2 2022, but it doesn't calculate Q3 correctly as I do not have the data for August/September.

The second (Average Connector Usage) is not as precise in the result but it calculates the Quarter averages correctly.

These are the results.

It all started with my base function to create further functions:

SDR ID average per CP ID =

AVERAGEX(

KEEPFILTERS(VALUES('FACT TABLE'[CP ID])),

CALCULATE(COUNTA('FACT TABLE'[SDR ID]))

)

SDR ID is my unique transaction number and CP ID is my unique location number.

I have tried many measures and these two are the closest to return correct results.

No.1.

Average COALESCE =

IF (

NOT ISEMPTY ( 'FACT TABLE' ),

AVERAGEX (

VALUES ( 'Calendar'[Date] ),

COALESCE ( [SDR ID average per CP ID], 0 )

)

No.2.

Average Connector Usage =

IF (

NOT ISEMPTY ( 'FACT TABLE' ),

VAR minDate =

MIN ( 'FACT TABLE'[Start Date] )

VAR maxDate =

MAX ( 'FACT TABLE'[Start Date] )

RETURN

CALCULATE (

AVERAGEX (

VALUES ( 'Calendar'[Date] ),

COALESCE ( [SDR ID average per CP ID], 0 )

DATESBETWEEN ( 'Calendar'[Date], minDate, maxDate )

)

Would anyone have and idea hot to fix it please?
Thank you kindly,
J

Re: help with my Coalesce

tamerj1 — Wed, 24 Aug 2022 10:53:24 GMT

Hi Anonymous
Please try

Average COALESCE = IF ( NOT ISEMPTY ( 'FACT TABLE' ), AVERAGEX ( SUMMARIZE ( 'FACT TABLE', 'FACT TABLE'[Column1], 'Calendar'[Year], 'Calendar'[Quarter] ), COALESCE ( [SDR ID average per CP ID], 0 ) ) )

Where 'FACT TABLE'[Column1] is the column you are placing in the rows of the pivot table.

Re: help with my Coalesce

Anonymous — Wed, 24 Aug 2022 11:43:27 GMT

Thank you tamerj1,
Much appreciated but the Average COALESE2 is giving me some weird totals.

Re: help with my Coalesce

tamerj1 — Wed, 24 Aug 2022 12:07:14 GMT

Hi Anonymous
This is highly dependant on the existing filter context. When you change the filter context you have to expect different results. The filter context in the pivot table screenshot is different than the filter context in the matrix screenshot. Also I have noticed that there are actually two columns placed in the rows of the matrix. Both columns must be included in the SUMMARIZE table. Would you please provide the names of these columns in the form TableName[ColumName] in order to support you further. Thank you for your Patience.

Re: help with my Coalesce

daXtreme — Wed, 24 Aug 2022 12:14:03 GMT

Hi there.

What's the explanation of the following line in [SDR ID average per CP ID]?

KEEPFILTERS(VALUES('FACT TABLE'[CP ID]))

Why do you think you need KEEPFILTERS here?

Re: help with my Coalesce

Anonymous — Wed, 24 Aug 2022 12:16:36 GMT

Thank you tamerj1 for your patience,
My columns are

'FACT TABLE'[Charger Type]

then

'FACT TABLE'[Connector]

Re: help with my Coalesce

daXtreme — Wed, 24 Aug 2022 12:19:59 GMT

It's pretty much obvious why the two return different results. The first one works with all dates from Calendar visible in the current context, even though there are no values for the dates in the fact table. On top of that, using COALESCE makes sure that such dates are assigned 0 as the value instead of BLANK, which makes the value of the average go down.

On the other hand, the second measure looks at the data in the fact table and retrieves the min and max dates from there. Then adjusts the context to only use the dates from Calendar that are between those 2 dates, so some dates from the Calendar might be rejected (and surely are since you're getting different results!). Still, you are using COALESCE which means you're treating BLANKS as 0's. Not sure if this is what you want because then your averages go down...

Re: help with my Coalesce

tamerj1 — Wed, 24 Aug 2022 12:31:37 GMT

Hi Anonymous
PLease try

verage COALESCE = IF ( NOT ISEMPTY ( 'FACT TABLE' ), AVERAGEX ( SUMMARIZE ( 'FACT TABLE', 'FACT TABLE'[Charger Type], 'FACT TABLE'[Connector] ), COALESCE ( [SDR ID average per CP ID], 0 ) ) )

Re: help with my Coalesce

Anonymous — Wed, 24 Aug 2022 12:30:07 GMT

hi daXtreme,
Thank you.
I need the SDR ID average per CP ID becasue I need the average unique SDR ID per CP ID.
Then I need average use of the unique usage per CP ID - my columns: Charger Type & Connector
I need COALESCE to give me correct averages per day including 0's in which the Charger Type & Connector weren't used that day, but I don't want to account for the Months in the Quarter, like Q3 August/September.
I hope this makes sense
See, if I use my Average COALESCE it just gives me the accurate calculations, but it doesn't account for the additional months in Q1 or Q3

Re: help with my Coalesce

Anonymous — Wed, 24 Aug 2022 12:35:05 GMT

Hi tamerj1,
Thank you kindly, but Nope. Your new function Average COALESCE3 returns some weird totals.

Average COALESCE3 =
IF (
NOT ISEMPTY ( 'FACT TABLE' ),
AVERAGEX (
SUMMARIZE (
'FACT TABLE',
'FACT TABLE'[Charger Type],
'FACT TABLE'[Connector]
),
COALESCE ( [SDR ID average per CP ID], 0 )
)
)

Re: help with my Coalesce

tamerj1 — Wed, 24 Aug 2022 12:49:14 GMT

Anonymous
I think you are looking for a daily average.

Please try

verage COALESCE = IF ( NOT ISEMPTY ( 'FACT TABLE' ), AVERAGEX ( SUMMARIZE ( 'FACT TABLE', 'FACT TABLE'[Charger Type], 'FACT TABLE'[Charger Type], 'Calendar'[Date] ), COALESCE ( [SDR ID average per CP ID], 0 ) ) )

Re: help with my Coalesce

Anonymous — Wed, 24 Aug 2022 14:19:42 GMT

🙂 Thank you tamerj1
Your 'verage COALESCE' is not working I'm afarid.
Thank ou so much for trying.

verage COALESCE =

IF (

NOT ISEMPTY ( 'FACT TABLE' ),

AVERAGEX (

SUMMARIZE (

'FACT TABLE',

'FACT TABLE'[Charger Type],

'FACT TABLE'[Connector],

'Calendar'[Date]

COALESCE ( [SDR ID average per CP ID], 0 )

)

Re: help with my Coalesce

tamerj1 — Wed, 24 Aug 2022 16:23:10 GMT

Anonymous

Thank you for your patience. Please try

verage COALESCE = AVERAGEX ( SUMMARIZE ( 'FACT TABLE', 'FACT TABLE'[Charger Type], 'FACT TABLE'[Charger Type] ), CALCULATE ( IF ( NOT ISEMPTY ( 'FACT TABLE' ), AVERAGEX ( VALUES ( 'Calendar'[Date] ), COALESCE ( [SDR ID average per CP ID], 0 ) ) ) ) )

Re: help with my Coalesce

Anonymous — Thu, 25 Aug 2022 10:07:17 GMT

Hi tamerj1,

Thank you;
Although the 'erage COALESCE' calculates the averages correctly, the totals are incorrect.

I have amended your function (where in red, as I was receiving an error when used

'FACT TABLE'[Charger Type] twice.

erage COALESCE =

AVERAGEX (

SUMMARIZE (

'FACT TABLE',

'FACT TABLE'[Charger Type],

'FACT TABLE'[Connector]

CALCULATE (

IF (

NOT ISEMPTY ( 'FACT TABLE' ),

AVERAGEX (

VALUES ( 'Calendar'[Date] ),

COALESCE ( [SDR ID average per CP ID], 0 )

)

Re: help with my Coalesce

tamerj1 — Thu, 25 Aug 2022 10:23:12 GMT

Anonymous

Yes this supposed to work in the previous tmatrix where you don't have the year and the quarter. Now you need to add year and quarter columns to the SUMMARIZE

Re: help with my Coalesce

Anonymous — Thu, 25 Aug 2022 10:37:40 GMT

Thank you tamerj1,
Please assist arranging this. Thank you kindly.

Re: help with my Coalesce

tamerj1 — Thu, 25 Aug 2022 11:05:32 GMT

Anonymous

apologies I'm outside the office now and replying through my phone.

No just summarize the fact table using the columns from both fat table and date table. Also please clarify if at the total levels you want to see the average of the averages or the sum of the averages?

Re: help with my Coalesce

Anonymous — Thu, 25 Aug 2022 11:40:45 GMT

Hi tamerj1,
Thank you. I would ideally like to see both versions: the average of the averages and the sum of the averages. To have the option to choose the version would make my boss extremely happy.

First the formula must give us the correct results; It feels like we are really close.
I would really, trully appreciate if you could assist me with this and send me both options for the formula when you are back at your desk.

Best,
J

PREVIEW

Re: help with my Coalesce

tamerj1 — Thu, 25 Aug 2022 12:27:03 GMT

Hi Anonymous
Sorry, I just returned back to office.

Please try the following for average of the averages and sum of the averages.

verage COALESCE = AVERAGEX ( SUMMARIZE ( 'FACT TABLE', 'Calendar'[Year], 'Calendar'[Quarter], 'FACT TABLE'[Charger Type], 'FACT TABLE'[Connector] ), CALCULATE ( IF ( NOT ISEMPTY ( 'FACT TABLE' ), AVERAGEX ( VALUES ( 'Calendar'[Date] ), COALESCE ( [SDR ID average per CP ID], 0 ) ) ) ) )

Sum COALESCE = SUMX ( SUMMARIZE ( 'FACT TABLE', 'Calendar'[Year], 'Calendar'[Quarter], 'FACT TABLE'[Charger Type], 'FACT TABLE'[Connector] ), CALCULATE ( IF ( NOT ISEMPTY ( 'FACT TABLE' ), AVERAGEX ( VALUES ( 'Calendar'[Date] ), COALESCE ( [SDR ID average per CP ID], 0 ) ) ) ) )

Re: help with my Coalesce

Anonymous — Thu, 25 Aug 2022 13:07:07 GMT

Hi tamerj1,

It's almost perfect.
Forget the verage COALESCE; Must focus on Sum COALESCE =

If we take Q3 for example, we only have July.
The formula now gives us correct everything, including correct total for Jul, but I need Q3 to display the July total not Q3 total.
The same with Q1.
I hope this makes sense.
Please see if you can figure this out.
Thank you thank you thank you.
J