Forum Discussion
Aggregate a measure after Filtering
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
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,
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 "Hi,
Share the link from where i can download your PBI file.
