topic Re: Rating system not giving me a total in DAX Commands and Tips

Rating system not giving me a total

TariqArnabi — Wed, 01 Feb 2023 21:57:20 GMT

I'm looking to create a rating system that will display the overall health of a division
example; anything greater than 80% is 1 point, 50% - 79% will be .5 points, 49% and less is 0 points.

i created the following dax which was able to give me the correct values, but i was not able to get the table to calculate a total, its giving me an average

Overall point =

VAR _Claims = SWITCH( TRUE(),

[% of Claims Assigned] >= .80, 1,

[% of Claims Assigned] >= .60 , .5,

)

VAR _Risk = SWITCH( TRUE(),

[% of Risk Assigned] >= .80, 1,

[% of Risk Assigned] >= .60 , .5,

)

VAR _Casualty = SWITCH( TRUE(),

[% of Casualty Assigned] >= .80, 1,

[% of Casualty Assigned] >= .60 , .5,

)

VAR _Property = SWITCH( TRUE(),

[% of Property Assigned] >= .80, 1,

[% of Property Assigned] >= .60 , .5,

)

VAR _Primary = SWITCH( TRUE(),

[% of Primary Assigned] >= .80, 1,

[% of Primary Assigned] >= .60 , .5,

)

VAR _Cyber = SWITCH( TRUE(),

[% of Cyber Assigned] >= .80, 1,

[% of Cyber Assigned] >= .60 , .5,

)

VAR _Total = CALCULATE(_Claims + _Risk + _Casualty + _Property + _Primary +_Cyber)

RETURN

_Claims + _Risk + _Casualty + _Property + _Primary +_Cyber

Expected visual the total is 32.5

current visual is giving an average

Re: Rating system not giving me a total

amitchandak — Thu, 02 Feb 2023 02:15:18 GMT

TariqArnabi , Change return like this and try

Sumx(Values(Table[Region]), calculate(_Claims + _Risk + _Casualty + _Property + _Primary +_Cyber))

Re: Rating system not giving me a total

Anonymous — Thu, 02 Feb 2023 02:32:42 GMT

Hi TariqArnabi ,

Please try using this measure.

total = IF ( HASONEVALUE ( 'Table'[Region] ), 'Table'[Overall], CALCULATE ( 'Table'[Overall], ALL ( 'Table' ) ) )

Or:

Measure = SUMX( SUMMARIZE( 'Table', 'Table'[Region], "total",[Overall] ), [total] )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

Re: Rating system not giving me a total

TariqArnabi — Thu, 07 Sep 2023 18:46:36 GMT

This worked. I also created a seperate measure to return the correct Total since SUMX does a Row by Row Evaluation. it was returning an incorrect Total.

I created a new measure if you need an overall TOTAL in the end.

Overall Point_Total =

IF (

ISINSCOPE(Organization[Region]),

[Overall point], ----- the Variable table created initially

SUMX( SUMMARIZE( Table, Table[Region],
"__Value",
[Overall point]), [__Value]))