Create a query to avoid using groups

[Anonymous]

Hello,

In my report, I have data about my production status by production line.

I have already a query with each of the lines that I have (single values) - ex. line A, line B, line C, line D.

However, sometimes, in our plan, our lines are grouped - ex. lineA/line B, line C, line D - and in the next week, I have instead a different group in our production plan: - ex. line A, line B/line C, line D. Since, the groups are changing for the same line ( I can have line B, lineA/line B or line B/lineC), I can't use the function group in my report.

How can I proceed to avoid changing the groups every week mannually? Should I create a new query? How?

Thank you

[V-lianl-msft]

Hi @Anonymous,

Can you provide the results you expect?

Best Regards,

Liang

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

[Anonymous]

Hello,

So, the reasults I would like to have are:

Week - Line- Plan Qtt - Prod Qtt

51 - A - 110 - 90

51 - B/C- 200- 130

51- D- 65- 60

Thank you

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

This may be a little complicated. In order to find the sum of "lines", you need to first pivot table of "real", and then merge pivot table with "plan" table through the week column.

Create a calculated column to find the quality of the production line corresponding to each week .

Use if function to create a measure to return value.

Please refer to the pbix.

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

[RobbeVL]

Hi there,

This problem you could solve in PowerQuery.

Just do a "Replace value"  of "/ "to ",", and that should do the trick.

Let me know if this worked!

Robbe

[Anonymous]

Sorry, but I can't understand.

My data is like this:

Production Plan:

Line - Quantity - Week

A - 110 - 51

B/C - 200 - 51

D - 60 - 51

A/B - 150 - 52

C-120-52

D-65-52

And my real production query have values like the following:

Line - Quantity - week

A- 90- 51

B - 30 - 51

C - 100 - 51

D- 60 - 51

How can I compare the real production with the plan filtering the lines of the plan, without changing manually the groups?

Thank you,