Total not adding up

[Agent47]

As you can see , the total is not adding up.

This is the model view 

Measure_RA_SUM =

CALCULATE(

DISTINCTCOUNT('Risk Assesment'[Ra_Code]),

TREATAS(VALUES('Hazard'[Risk]), 'Hazard'[Risk])

)

Risk is created from MAx_Risk_Rating 

where there are multiple contradicting data but it is worked upon by custom SQL query which created HAZARD

Select CASE 

     When Max_Risk_Rating LIKE '%Very%Low%' THEN 'Very Low'

   When Max_Risk_Rating LIKE '%VERY%LOW%' THEN 'Very Low'

WHEN Max_Risk_Rating LIKE '%Very%High%' THEN 'Very High'

WHEN Max_Risk_Rating LIKE '%VERY%HIGH%' THEN 'Very High'

WHEN Max_Risk_Rating LIKE '%High%' THEN 'High'

WHEN Max_Risk_Rating LIKE '%HIGH%' THEN 'High'

WHEN Max_Risk_Rating LIKE '%Low%' THEN 'Low'

WHEN Max_Risk_Rating LIKE '%LOW%' THEN 'Low'

WHEN Max_Risk_Rating LIKE '%Medium%' THEN 'Medium'

WHEN Max_Risk_Rating LIKE '%MEDIUM%' THEN 'Medium'

when Id = 99990000076349  THEN 'Low'

WHEN Max_Risk_Rating is null THEN 'Unknown'

WHEN Max_Risk_Rating like '%Null%' THEN 'Unknown'

else Max_Risk_Rating end as Risk

,Ra_Master_Id , Max_Risk_Rating 

FROM LPSQ.VW_DM_LPSQ_RISK_ASSESSMENTS_HAZARD
--------------------------------------------------
Please let me know if any more info is needed . Thanks .
@rajendraongole1 

[danextian · Accepted solution]

Hi @Agent47 

DISTINCTCOUNT is the hint. An Ra_Code could have more than one distinct risk values. At Risk level, there are 4, 49, 52, 4 and 21 distinct Ra_Code for each distinct Risk value. However, at Vessel_name level there are only 63 and not the sum of the indivdual breakdown

In the screenshot below, you can see there are only 3 distinct codes so these will be count for Level2

If you want your measure to evaluated for each distinct column values in your viz, use SUMX over a virtual/temporary table. Example:

SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'data', 'data'[type], 'data'[name], 'data'[risk] ),
        "@value", [Measure_RA_SUM]
    ),
    [@value]
)