Forum Discussion

bslintx's avatar
bslintx
Frequent Visitor
1 year ago
Solved

Power BI Desktop Summarized Table with custom function

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

 

 

Need Help
  • OwenAuger's avatar
    OwenAuger
    1 year ago

    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

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    1 year ago

    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's avatar
      bslintx
      Frequent Visitor
      1 year ago

      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: