Power BI Desktop Summarized Table with custom function

[bslintx]

Hi,

I am trying to dynamically create a summarized query/table that groups/summarizes a "vulnerabilities" table.

I have it to where it groups ip addresses/organizations.... and breaks out/summarizes the "severity" column >  (Low, Medium,High,Critical) BUT I am needing a column that is a derivative of the severity breakout into a custom formula - see "VPH formula" below:

 Host_Totals = SUMMARIZE(
 
 'Vulnerabilities',
  Vulnerabilities[Organization],
  Vulnerabilities[IP Address],
  "Low", CALCULATE ( COUNTROWS ( Vulnerabilities ), 'Vulnerabilities'[Severity] = "Low" ) + 0,
  "Medium", CALCULATE ( COUNTROWS (Vulnerabilities ), 'Vulnerabilities'[Severity] = "Medium" ) + 0,
  "High", CALCULATE ( COUNTROWS ( Vulnerabilities ), 'Vulnerabilities'[Severity] = "High" ) + 0,
  "Critical", CALCULATE ( COUNTROWS (Vulnerabilities ), 'Vulnerabilities'[Severity] = "Critical" ) + 0,
  "Total", COUNTROWS(Vulnerabilities),
  "VPH",  ((Low) + (Medium * 4) + (High * 10) + (Critical * 10)) / 15 
   
   )

Desired result:

I have used CALCULATE to add up COUNTROWS and divided by 15....but turns to a whole number and it is not summarized in the bottom of the table

Thanks

[OwenAuger · Accepted solution]

Hi @bslintx 

I would recommend rewriting like this:

Host_Totals =
GENERATE (
    SUMMARIZE (
        Vulnerabilities,
        Vulnerabilities[Organization],
        Vulnerabilities[IP Address]
    ),
    VAR Low =
        CALCULATE ( COUNTROWS ( Vulnerabilities ), 'Vulnerabilities'[Severity] = "Low" ) + 0
    VAR Medium =
        CALCULATE (
            COUNTROWS ( Vulnerabilities ),
            'Vulnerabilities'[Severity] = "Medium"
        ) + 0
    VAR High =
        CALCULATE (
            COUNTROWS ( Vulnerabilities ),
            'Vulnerabilities'[Severity] = "High"
        ) + 0
    VAR Critical =
        CALCULATE (
            COUNTROWS ( Vulnerabilities ),
            'Vulnerabilities'[Severity] = "Critical"
        ) + 0
    VAR Total =
        CALCULATE ( COUNTROWS ( Vulnerabilities ) )
    VAR VPH = ( ( Low ) + ( Medium * 4 ) + ( High * 10 ) + ( Critical * 10 ) ) / 15
    RETURN
        ROW (
            "Low", Low,
            "Medium", Medium,
            "High", High,
            "Critical", Critical,
            "Total", Total,
            "VPH", VPH
        )
)

By using GENERATE with ROW in this way, it is possible to declare variables within the second argument of GENERATE which can reference each other, then return the required column values within ROW.

See this article for a discussion of this method:

https://www.sqlbi.com/articles/using-generate-and-row-instead-of-addcolumns-in-dax/

This article discusses why extension columns should not be added with SUMMARIZE itself:

https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/

Does the above work for you?

Regards

[bslintx]

Owen,

That was EXACTLY what I wanted to do. I tried to assign variables in my example but as you know I was unable to but thankfully you put me in the right direction and solution - thank you!

~ Brian

Note: 

The only thing that was needed was to tweak the Host_Totals Visual table ..... The summarization did not come up and I had to manually assign to SUM for Low, Medium, High, Critical, Total and Avg for VPH

I included image this in case someone uses this solution and notices no totals wew populated: