Forum Discussion

Yonatan1984's avatar
Yonatan1984
Helper I
4 years ago
Solved

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.

I 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:

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

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

 

 

DAX Data Table
POWER BI DATA VİEW
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  Yonatan1984 ,

    I created some data:

    DIVUACH_PSULIM：

    NEW_DATE：

    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:

     

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Yonatan1984 ,

    I created some data:

    DIVUACH_PSULIM：

    NEW_DATE：

    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:

     

    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

  • Gabriel_Walkman's avatar
    Gabriel_Walkman
    Continued Contributor
    4 years ago

    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 🙂