Forum Discussion

cglinn's avatar
cglinn
Regular Visitor
5 years ago

Aggregate a measure after Filtering

I'm very new to Power BI.  I've read through many of the posts here but haven't been able to find a resolution, so hoping that someone can help.

 

I'm trying to populate a single line Card Visual based on the selection of dates where the sum of the variance for the combination of Region, Group, and selected dates is negative, as shown below.

 

 

 

 
 

Any suggestions?

 

Need Help

11 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    If you provide example data in copy/paste format, a specific expression can be provided.  However, you can try a pattern like this to get your result.

     

    Just Negative =
    VAR summary =
        ADDCOLUMNS (
            SUMMARIZE ( Table, Table[Region], Table[Group], Date[YearMonth] ),
            "cVariance", [YourVarianceMeasure]
        )
    RETURN
        SUMX ( FILTER ( summary, [cVariance] < 0 ), [cVariance] )

     

    Pat

     

    • cglinn's avatar
      cglinn
      Regular Visitor
      5 years ago

      Pat,

       

      This solution is very close.  It works if just one month is selected.  However, if multiple months are selected, the identification of a negative value for the aggregated total for the multiple months is not working properly.  I've been able to get it to work if put in a table visualization with the Group included.  However, once it goes into a Card visualization the calculation does not work correctly. 

       

      The desired result would be if April and May are selected in the slicer then:

      For each combination of Region/Group, total the resulting value from if(sum(April Variance, May Variance)<0, sum(April Variance, May Variance),0) to create a single total number for the entire population (as shown on the last row below.

       

      Thanks again,

      • cglinn's avatar
        cglinn
        Regular Visitor
        5 years ago

        Everytime I try to copy/paste the data and post the reply I get an HTML error.  So, here's an attempt a delimited list:

         

        Region,Group,Month,Variance
        1,A,Apr-21,"($817,554)"
        1,A,May-21,"($1,013,666)"
        1,A,Jun-21,"($328,526)"
        1,B,Apr-21,"$231,961 "
        1,B,May-21,"$171,879 "
        1,B,Jun-21,"($99,629)"
        1,C,Apr-21,"$111,776 "
        1,C,May-21,"$6,153 "
        1,C,Jun-21,"$304,564 "
        1,D,Apr-21,"$110,964 "
        1,D,May-21,"($24,477)"
        1,D,Jun-21,"($22,846)"
        1,E,Apr-21,"($157,251)"
        1,E,May-21,"($60,183)"
        1,E,Jun-21,"($60,183)"
        1,F,Apr-21,"($1,567,083)"
        1,F,May-21,"$201,826 "
        1,F,Jun-21,"$33,638 "

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    You could try:

    Expected card total =

    VAR _Variance = SUM(Table[Variance])

    RETURN

    CALCULATE(_Variance,  FILTER(Table, _Variance < 0))