topic Re: calculate average nr of claims per brand per period in DAX Commands and Tips

calculate average nr of claims per brand per period

Anonymous — Fri, 08 Mar 2024 09:30:51 GMT

hi all,

In this case, we have brands, periods (like 202301) and claims. The claims is in fact a distinctcount of the orders and thus a measure. What i am trying to get is an average per brand per period. The data looks like this:

Period Brand Claims

202311 AAAA 10

202311 BBBB 2

202311 CCCC 6

202312 DDDD 4

202312 CCCC 8

202401 BBBB 2

202401 AAAA 18

so in total we have 50 claims/7= 7.14 claims on average

But for the period of 202311 we have 18 claims which gives 6 on average

202312= 12 in total, 6 on average

The period is always selected via a slicer .

How can i create this Average per brand per period calculation in DAX....

thnx!!

Re: calculate average nr of claims per brand per period

talespin — Fri, 08 Mar 2024 10:08:03 GMT

hi Anonymous

You can use somthing like this, I don't what is inside your measure [Claims] or your data model, so I can only best guess.

AVERAGEX(
VALUES(Period),
[Claims]
)

Re: calculate average nr of claims per brand per period

Anonymous — Fri, 08 Mar 2024 11:12:56 GMT

hi Talespin,

thanks for swift response but it is not what i am looking for.

the challenge is to count the number of identical periods and summarize the nr of claims in that period and then divide the nrofclaims/countnrperiod

as also explained in my example.

but again, thanks for helping out!!

Re: calculate average nr of claims per brand per period

talespin — Fri, 08 Mar 2024 11:36:23 GMT

hi Anonymous ,,

See if this helps

Measure Avg =

VAR _SelectedPeriod = SELECTEDVALUE(Table2[Period])

VAR _CountPeriod = CALCULATE(COUNT(Table2[Period]), REMOVEFILTERS(), VALUES(Table2[Period]))

VAR _SumClaim = CALCULATE( SUM(Table2[Claim]), REMOVEFILTERS(), Table2[Period] = _SelectedPeriod)

RETURN DIVIDE(_SumClaim, _CountPeriod)

Re: calculate average nr of claims per brand per period

Anonymous — Mon, 11 Mar 2024 13:38:42 GMT

hi Talespin,

thanks again for swift response, this is the dax script that you gave me:

Average Claim =

VAR _SelectedPeriod = SELECTEDVALUE('DSA SF_Case'[Create period])

VAR _CountPeriod = CALCULATE(COUNT('DSA SF_Case'[Create period]), REMOVEFILTERS(), VALUES('DSA SF_Case'[Create period]))

VAR _SumClaim = CALCULATE( DISTINCTCOUNT('DSA SF_Case'[CaseNumber]) , REMOVEFILTERS(), 'DSA SF_Case'[Create period] = _SelectedPeriod)

RETURN DIVIDE(_SumClaim, _CountPeriod)

Had to make 1 modification as the field Claims in my table is already a meausre--> Distinctcount(DSA SF_case [Casenr] and with this measure i cannot add a sum in the script as suggested by you. That is why i switched to DISTINCTCOUNT but in that case it gives empty values....

Any idea?

thnx!!!

Re: calculate average nr of claims per brand per period

talespin — Tue, 12 Mar 2024 02:48:56 GMT

hi Anonymous

Please share Claims measure definition.

Re: calculate average nr of claims per brand per period

Anonymous — Tue, 12 Mar 2024 08:13:33 GMT

hi Talespin,

appreciate your efforts to solve this case!!

This is how the measure Nr of Claims is created (it is a measure)--> Nr of Claims=DISTINCTCOUNT('DSA SF_Case' [CaseNumber for Calculation]). The casenumber for Calculation is a field that contains a case nr which is not a string but it is an int. By default the CaseNumber is a string.

Re: calculate average nr of claims per brand per period

talespin — Tue, 12 Mar 2024 09:15:39 GMT

hi Anonymous

I have used this as sample data based on what you have mentioned.

PeriodBrandCaseNo

202311	AAAA	1
202311	AAAA	2
202311	AAAA	3
202311	AAAA	4
202311	AAAA	5
202311	AAAA	6
202311	AAAA	7
202311	AAAA	8
202311	AAAA	9
202311	AAAA	10
202311	BBBB	11
202311	BBBB	12
202311	CCCC	13
202311	CCCC	14
202311	CCCC	15
202311	CCCC	16
202311	CCCC	17
202311	CCCC	18
202312	DDDD	19
202312	DDDD	20
202312	DDDD	21
202312	DDDD	22
202312	CCCC	23
202312	CCCC	24
202312	CCCC	25
202312	CCCC	26
202312	CCCC	27
202312	CCCC	28
202312	CCCC	29
202312	CCCC	30

----------------------------------------------------------------------------------------

Claim = DISTINCTCOUNT(Tbl[CaseNo])

----------------------------------------------------------------------------------------

Average =

VAR _SelectedPeriod = SELECTEDVALUE(Tbl[Period])

VAR _CountPeriod = CALCULATE(COUNTX(SUMMARIZE(Tbl, Tbl[Period],Tbl[Brand]), [Period]), REMOVEFILTERS(), VALUES(Tbl[Period]))

VAR _SumClaim = CALCULATE( SUMX(Tbl, [Claim]), REMOVEFILTERS(), VALUES(Tbl[Period]))

RETURN DIVIDE(_SumClaim, _CountPeriod)

----------------------------------------------------------------------------------------

Re: calculate average nr of claims per brand per period

Anonymous — Tue, 12 Mar 2024 12:28:45 GMT

Hi Talespin,

in line of what you suggested, this is my measure:

Average Claim =

VAR _SelectedPeriod = SELECTEDVALUE('DSA Salesforce_Case'[Create period])

VAR _CountPeriod = CALCULATE(COUNTX(SUMMARIZE('DSA Salesforce_Case','DSA Salesforce_Case'[Create period],'DSA Salesforce_Case'[Brand__c]), 'DSA Salesforce_Case'[Create period]), REMOVEFILTERS(), VALUES('DSA Salesforce_Case'[Create period]))

VAR _SumClaim = CALCULATE(SUMX('DSA Salesforce_Case',[Nr of Claims New]), REMOVEFILTERS(), VALUES('DSA Salesforce_Case'[Create period]))

RETURN DIVIDE(_SumClaim, _CountPeriod)

The output without Brand is presenting this:

if i am correct than over the 2 months, i should have an average of 1642 and not 1154, correct?

then if i add the brand, this is the result:

Not sure where the number 1207.67 for the period 202401 stands for.... and thus the end result is also not correct.

Hope you can help...

thnx!!!!

Re: calculate average nr of claims per brand per period

talespin — Tue, 12 Mar 2024 13:28:45 GMT

hi Anonymous

Can you please share pbix file with mock data.

Re: calculate average nr of claims per brand per period

Anonymous — Tue, 12 Mar 2024 14:16:37 GMT

hi Talespin,

is there a way to share this information??

thnx!!

Re: calculate average nr of claims per brand per period

talespin — Wed, 13 Mar 2024 04:43:22 GMT

hi Anonymous

You could upload pbix file anywhere and share the link, there are so many options.

Re: calculate average nr of claims per brand per period

Anonymous — Wed, 13 Mar 2024 06:28:00 GMT

hi Talespin,

if i want to share this with you, i need to have a email address, otherwise the system will not allow me to share the info....

sorry, maybe i am doing something wrong but am trying to share the pbix via a Onedrive folder

Re: calculate average nr of claims per brand per period

talespin — Wed, 13 Mar 2024 07:44:51 GMT

hi Anonymous

There are many sites where you can upload file and share links without requiring email address.

Upload Files Of Any Size – Free Up To 5 GB - Filemail

File.io

Google and you will find many such sites.

Re: calculate average nr of claims per brand per period

Anonymous — Wed, 13 Mar 2024 10:29:16 GMT

hi Talespin,

thanks for the tip on sharing files (never used it before) but you can grab the file via this link:

https://www.filemail.com/d/qmdrotarnwtxfxq

again thanks for helping out, appreciate it!!!

Re: calculate average nr of claims per brand per period

talespin — Wed, 13 Mar 2024 14:38:08 GMT

hi Anonymous

Please check this.

Created this measure, remove GT if you do not require Average under Totals.

Average Claim =

VAR _SelectedPeriod = SELECTEDVALUE('DSA Salesforce_Case'[Create period])

VAR _CountBrand = CALCULATE( DISTINCTCOUNT('DSA Salesforce_Case'[Brand__c]), ALLSELECTED('DSA Salesforce_Case'),'DSA Salesforce_Case'[Create period] = _SelectedPeriod )

VAR _SummTbl =

SUMMARIZE(

'DSA Salesforce_Case',

'DSA Salesforce_Case'[Create period], 'DSA Salesforce_Case'[Brand__c]

)

VAR _CountBrandGT= COUNTX(_SummTbl, [Create period])

VAR _SumClaim = CALCULATE(

[Nr of Claims],

ALLSELECTED('DSA Salesforce_Case'),

'DSA Salesforce_Case'[Create period] = _SelectedPeriod

)

VAR _SumClaimGT = [Nr of Claims]

RETURN IF(

HASONEFILTER('DSA Salesforce_Case'[Create period]),

DIVIDE(_SumClaim, _CountBrand),

DIVIDE(_SumClaimGT, _CountBrandGT)

)

Uploaded file here

https://drive.google.com/file/d/1Nq7650H4tOf2K-qs8hbpXLNeE6oibZG3/view?usp=sharing

Re: calculate average nr of claims per brand per period

Anonymous — Wed, 13 Mar 2024 15:09:16 GMT

hi Talespin,

thanks for all your time and efforts, amazing!!! But the result is as expected and that is for me very important.... Thanks a lot!!!

Maarten

Re: calculate average nr of claims per brand per period

talespin — Thu, 14 Mar 2024 04:48:21 GMT

hi Anonymous

You're welcome.