Forum Discussion

npowers2's avatar
npowers2
Frequent Visitor
5 years ago

FILTER Results From Multiple Columns within a Summary Table

I created a Summary Table from my original data set using ROLLUP to generate subtotals for a given week. I am now trying to calculate the average of the resulting subtotals. When using the following I can get the average of the section highlighted in Red on the image below:

UCL = CALCULATE(AVERAGE('Summary Table'[Row Count]), 
        FILTER('Summary Table', 'Summary Table'[Total] = FALSE()))

However when I try to get the average for the Blue section (i.e. the subtotal from the summary table) the result returns a BLANK:

UCL = CALCULATE(AVERAGE('Summary Table'[Row Count]), 
        FILTER('Summary Table', 'Summary Table'[Total] = FALSE()&& 
            'Summary Table'[Profile Subtotal] = TRUE()))

 

Is there a way to apply the filter such that I get the average of the subtotal?

 

Thank you for any help.

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    npowers2 , seem strage. Formula seems fine at first look

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    As total is always false try like

    UCL = CALCULATE(AVERAGE('Summary Table'[Row Count]), 
        FILTER('Summary Table',
            'Summary Table'[Profile Subtotal] = TRUE()))
    • npowers2's avatar
      npowers2
      Frequent Visitor
      5 years ago

      amitchandak :

       

      Below is a link to the pbix file. I tried your suggestion, but it provided the sample BLANK result. 

      Example File 

       

      Thank you for your help.

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

        npowers2 , I checked the file. You have a filter of profile results. And Profile result is blank for the blue area. I removed the filter from the slicer and I can see 12 counts.