Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
TariqArnabi
Frequent Visitor

Rating system not giving me a total

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,
                0
                )
VAR _Risk       =   SWITCH( TRUE(),
                [% of Risk Assigned]  >= .80, 1,
                [% of Risk Assigned] >= .60 , .5,
                0
                )
VAR _Casualty   =   SWITCH( TRUE(),
                [% of Casualty Assigned]  >= .80, 1,
                [% of Casualty Assigned] >= .60 , .5,
                0
                )
VAR _Property   =   SWITCH( TRUE(),
                [% of Property Assigned]  >= .80, 1,
                [% of Property Assigned] >= .60 , .5,
                0
                )
VAR _Primary   =   SWITCH( TRUE(),
                [% of Primary Assigned]  >= .80, 1,
                [% of Primary Assigned] >= .60 , .5,
                0
                )
VAR _Cyber     =   SWITCH( TRUE(),
                [% of Cyber Assigned]  >= .80, 1,
                [% of Cyber Assigned] >= .60 , .5,
                0
                )        
VAR _Total      = CALCULATE(_Claims + _Risk + _Casualty + _Property + _Primary +_Cyber)
RETURN
_Claims + _Risk + _Casualty + _Property + _Primary +_Cyber

Expected visual the total is 32.5

TariqArnabi_0-1675288422710.png

current visual is giving an average

TariqArnabi_1-1675288520967.png

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@TariqArnabi , Change return like this and try

 

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

View solution in original post

3 REPLIES 3
v-tangjie-msft
Community Support
Community Support

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. 

amitchandak
Super User
Super User

@TariqArnabi , Change return like this and try

 

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

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]))

TariqArnabi_0-1694112290086.png

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.