Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
newgirl
Helper V
Helper V

Not Filtering Correctly

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.

 

positive1.JPG

Preview of my calendar table:

positive2.JPG

 

Preview of my Sheet1 (main table).  

Mobile expense - is their expense in their mobile

MSF - credit line

Difference - [MSF] less [Mobile Expense]

positive3.JPG

 

 

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.

positive3.1.JPG

 

 

Below are 3 samples (Oliver, Leonard, and Renelfa). For each sample, I will show how it appears in the matrix and in data view.

positive4.1.JPGpositive4.JPGpositive5.1.JPGpositive5.JPGpositive6.1.JPGpositive6.JPG

 

 

 

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
amitchandak
Super User
Super User

@newgirl , Try a measure like

 

sumx(filter(summarize(Table, Table[VP] , Table[Account Number] , Table[Assignee], "_1", [Difference]), [_1] <0 ),[_1])

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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])

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.