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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Yonatan1984
Helper I
Helper I

Improve visual table - problem with sum by dates

Hello everyone!

I'm new in power bi (expert in Excel) and I did an dashboard based on varied data sources (Share Point List, Oracle and more).

My dashboard presents the total manufactre work orders on the machines in my factory and also the invalid items that the QC department reject.

When the user pick a date to slice, the data that need to show is the work order that active in the machine, according to the slicer date, and the total amount manufactre an the invalid item for this day only.

My problem is when I make an visual table that need to shows the OEE of the machine in the amount of the invalid items, the visual table shows the total amount for the specific work order but nit according to the date slicer.

thought maybe to creat an marge table, but the data that shows for the invalid items is the total amount of all invalid items from day one.

Here is an screensot of the visual table:

Yonatan1984_0-1637139816842.png

Here is an screenshot of the invalid table according to the highlight exmple in the previous screenshot:

Yonatan1984_1-1637140019908.png

Here is an screenshot of the marging table and the sum of the invalid items column formula:

Yonatan1984_2-1637140141838.png

 

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Yonatan1984 ,

I created some data:

DIVUACH_PSULIM:

vyangliumsft_0-1637633710396.png

NEW_DATE:

vyangliumsft_1-1637633710398.png

Here are the steps you can follow:

Take the NewDate column of the NEW_DATE table as the slicer.

1. Create measure.

Measure =
VAR _Select=SELECTEDVALUE(NEW_DATE[NewDate])
return
CALCULATE(SUM('DIVUACH_PSULIM'[Invalid_amount]),FILTER(ALL('DIVUACH_PSULIM'),'DIVUACH_PSULIM'[Modified]=_Select&&'DIVUACH_PSULIM'[work_order]=MAX('DIVUACH_PSULIM'[work_order])))

2. Result:

vyangliumsft_2-1637633710400.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

Hi  @Yonatan1984 ,

I created some data:

DIVUACH_PSULIM:

vyangliumsft_0-1637633710396.png

NEW_DATE:

vyangliumsft_1-1637633710398.png

Here are the steps you can follow:

Take the NewDate column of the NEW_DATE table as the slicer.

1. Create measure.

Measure =
VAR _Select=SELECTEDVALUE(NEW_DATE[NewDate])
return
CALCULATE(SUM('DIVUACH_PSULIM'[Invalid_amount]),FILTER(ALL('DIVUACH_PSULIM'),'DIVUACH_PSULIM'[Modified]=_Select&&'DIVUACH_PSULIM'[work_order]=MAX('DIVUACH_PSULIM'[work_order])))

2. Result:

vyangliumsft_2-1637633710400.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Its work!

Thanks!!!

Gabriel_Walkman
Continued Contributor
Continued Contributor

Hi there.

 

I can just say that I recommend some beginner tutorials on DAX CALCULATE with filters. Should be plenty available with a simple google.

Also, I think you mean "Merge", not "Marge" which is a womans name 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors