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
Asfaa5
Frequent Visitor

Help in Dax Measure

Hi Everyone, i want to create a Dax Measure for Average Severity Score for each category by grouping risk type.

 

My Fact_Claim_Service_Provider looks like this,

 

Service_Center_IDRisk_Type_IDSeverity ScoreSeverity BenchmarkClaim Count
328411431123695
328411431153694
3284114310536910
328411879132720
328411158432715
328511431123695
328511431153694
3285114310536910
328511879132720
328511158432715
328711431123695
328711431153694
3287114310536910
328711879132720
328711158432715

 

I need to group Risk_Type_ID and arrive average severity score for each service center.

Ex : for Service_Center_ID 3284, formula Average Severity Score =((112+115+105)*19/54)+(91*20/54)+(84*15/54)

The output 173.84. Same need to be done for all service center.

Can anyone help me on the dax measure.

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, @Asfaa5 

Please try the below measure.

 

Avg Severity Score =
VAR currentcenterid =
MAX ( 'Table'[Service_Center_ID] )
VAR newtable =
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[Service_Center_ID] = currentcenterid ),
'Table'[Risk_Type_ID],
"@securityscore", SUM ( 'Table'[Severity Score] ),
"@claimcount", SUM ( 'Table'[Claim Count] ),
"@claimcountall",
CALCULATE (
SUM ( 'Table'[Claim Count] ),
ALLEXCEPT ( 'Table', 'Table'[Service_Center_ID] )
)
)
RETURN
SUMX ( newtable, DIVIDE ( [@securityscore] * [@claimcount], [@claimcountall] ) )

 

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Asfaa5
Frequent Visitor

@Jihwan_Kim : Thanks. it worked superb.

Jihwan_Kim
Super User
Super User

Hi, @Asfaa5 

Please try the below measure.

 

Avg Severity Score =
VAR currentcenterid =
MAX ( 'Table'[Service_Center_ID] )
VAR newtable =
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[Service_Center_ID] = currentcenterid ),
'Table'[Risk_Type_ID],
"@securityscore", SUM ( 'Table'[Severity Score] ),
"@claimcount", SUM ( 'Table'[Claim Count] ),
"@claimcountall",
CALCULATE (
SUM ( 'Table'[Claim Count] ),
ALLEXCEPT ( 'Table', 'Table'[Service_Center_ID] )
)
)
RETURN
SUMX ( newtable, DIVIDE ( [@securityscore] * [@claimcount], [@claimcountall] ) )

 

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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.

Top Solution Authors