I’m trying to calculate a specific type of what-if scenario

[LightSQR]

What is the project?

I’m developing a solution to show management our overall risk posture based on data coming in from audits.  Each finding has a score associated with it (1=Low 2=Medium, 3=High, 4=Critical, but the score number can be decimal values in between these).  Also, these findings have a status, so once they are actually remediated in the wild, they don’t show up on a report. 

I’ve created reports that visually show our average risk score categorized out in several different ways (what type of asset, which overall risk category, etc.). 

One of the other tables has projects, with a one-to-many relationship to the findings (so we can associate multiple findings to a project, which would identify what findings would be remediated if we ran with a proposed project).  This is related through an intermediary table FindingsToProjects.

We want to show what happens to our overall risk scores when a particular project is run.  So for example, if we run project XYZ (which would remediate finding B and finding C), how would our risk score look then?  So in the FindingsToProjects table we basically have this:

ProjectName	FindingName
Project XYZ	Finding B
Project XYZ	Finding C
Project LMN	Finding A

What is the problem I’m having?

I thought I had this all figured out, because when I deselect a project in the slicer on the report, the score changes, etc.  But what I realized is that it’s now just showing the average risk score of a filtered down set of risks. 

So for example, if this is my data (fictitious, but showing what I’m trying to do):

FindingName	RiskScore	FindingStatus	FindingClosedDate
Finding A	2.67	Closed	5/6/2024
Finding B	2.33	Open
Finding C	4.00	Open
Finding D	1.33	Open
Finding E	3.33	Open

In this case, the measure I created named AveRisk:

AveRisk = AVERAGE([RiskScore])

Would have an overall value of 2.73 (for example, in a Card visual, have this meause displaying Average).  Once I filter out the “Closed” in a visual, then the average would be 2.75. 

That works fine.  It’s when I use the slicer to deselect a project that the problem comes up.

If finding B and C are associated to the project “Project XYZ”, right now when I deselect that project in the slider it shows a new risk score of 2.33 (which is show the average of finding D and E, since B and C are filtered out and finding A is closed). 

What I *WANT* it to display is a score of 1.17 (in other words, Average of: Finding B (with a score of 0 since it would be remediated with that project XYZ), finding C (with a score of 0), finding C (1.33), and finding D (3.33)).  

I think I need to do this using some DAX / meaasures I'm just not thinking of yet, but not sure the best way to do it.  (Also, I have full ability to modify the source tables if needed, like adding new columns or whatever).   

Please help!!  And thanks in advance!

[v-yohua-msft · Accepted solution]

Hi, @LightSQR 

First create a measure to calculate the values of C and D, and then average the measure. Try the following DAX:

AdjustedRiskScore = 
VAR ProjectCount =
    COUNTROWS(
        FILTER(
            'FindingsToProjects',
            'FindingsToProjects'[FindingName] = SELECTEDVALUE('Findings'[FindingName])
        )
    )
RETURN
    IF(
        ProjectCount > 0,
        0, 
        SELECTEDVALUE('Findings'[RiskScore]) 
    )

AveRisk = AVERAGEX('Findings','Findings'[AdjustedRiskScore])

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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