Hi! I have two tables in my file: Calendar table and my main table. Goal of the report is to list down per month which employees are exceeding their mobile plan.
Preview of my calendar table:
Preview of my Sheet1 (main table).
Mobile expense - is their expense in their mobile
MSF - credit line
Difference - [MSF] less [Mobile Expense]
I created the matrix below with the following filters. I noticed that even though I filtered it to show [Difference] is less than 0 (meaning these are those who exceeded their mobile plan), there are some positive values that show up.
Below are 3 samples (Oliver, Leonard, and Renelfa). For each sample, I will show how it appears in the matrix and in data view.
I noticed that for these 3 samples, within one month, they have more than 1 account number. I'm not sure if this is what's causing the positive numbers to still show up in the matrix despite the filtering.
But basically, my question is how to show in the matrix those that have less than 0 in difference? Or what should my measure be, if needed?
Hope somebody can help me!
1 ACCEPTED SOLUTION
@newgirl , Try a measure like
sumx(filter(summarize(Table, Table[VP] , Table[Account Number] , Table[Assignee], "_1", [Difference]), [_1] <0 ),[_1])
@newgirl , Try a measure like
sumx(filter(summarize(Table, Table[VP] , Table[Account Number] , Table[Assignee], "_1", [Difference]), [_1] <0 ),[_1])
Thank you @amitchandak ! It worked!
For reference to other readers, here is the measure I made:
Measure =
SUMX(FILTER(SUMMARIZE(Sheet1,Sheet1[MonthYear],Sheet1[VP],Sheet1[Account Number],Sheet1[Assignee], "_1", SUM(Sheet1[Difference])), [_1] < 0),[_1])
