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

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
Agent47
Helper I
Helper I

Total not adding up

Agent47_0-1733374167520.png

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

Agent47_1-1733374221762.png

This is the model view 

Agent47_2-1733374254855.png

Measure_RA_SUM =
CALCULATE(
DISTINCTCOUNT('Risk Assesment'[Ra_Code]),
TREATAS(VALUES('Hazard'[Risk]), 'Hazard'[Risk])

 

)
Agent47_3-1733374336623.pngAgent47_4-1733374366720.png

Risk is created from MAx_Risk_Rating 

Agent47_5-1733374395227.png

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 
1 ACCEPTED SOLUTION
danextian
Super User
Super User

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

danextian_1-1733377909694.png

 

 

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

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

1 REPLY 1
danextian
Super User
Super User

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

danextian_1-1733377909694.png

 

 

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

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.