Forum Discussion

Agent47's avatar
Agent47
Helper I
1 year ago
Solved

Total not adding up

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 
Need Help
Tips and Tricks
Tutorial Requests
  • danextian's avatar
    danextian
    1 year ago

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

     

1 Reply

  • danextian's avatar
    danextian
    Super User
    1 year ago

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