Displaying data in Power Bi

[Cevaro]

Hi Guys,

I have such a dataset, which for now is only stretched in PQ.
We can see on it that there is a year, month, week, program, production line and Sum of Sales and Scrap

The point is that we can assign the Scrap value based on the ID - Year_Month_Week_ProdLine_Program, but we can assign Sales from the second table only based on Year_Month_Week_Program - this means that we do not have information about the Production line at the Sales level.

However, we have information about which Production Lines belong to which program (for example, the N2 program) (for example, BD2 and EPP2 belong to the N2 program).

I would now need to create a dashboard in Power BI that would be able to filter Sales for us based on the Production Line, but to give both records the same value of Sales, but not to add them.

For example, if we take the image below, we can see that the Sales value is the same in both records and is there twice. However, it should only be there once for both records combined. This means that not 2x value 426802.43 in both lines but as if only 1x value 426802.43 for admire records. The problem is that when I put it in the Dashboard, the filtering works at the Production Line level, but in the end it adds these values ​​together and comes out with 2 times the Sales value.

Is there a line of code in PowerBI that can display this the way I need it?

Thank you very much

[v-shex-msft]

Hi @Cevaro,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Notice: please remove the sensitive data from the sample file before sharing.

Regards,

Xiaoxin Sheng

[Cevaro]

So lets say we have this tables on screenshots ..

SALES DATA table - Help1 ID_KEY to merge with Source Table

SCRAP DATA - Help2 ID_KEY to merge with Source Table

Then we GroupBy them based on Help1 and Help2

 
Then we merge this Values with Source Table based on Help1 and Help2

As we can see, Help1 consists only of Year_Month_Week_Program but Help2 consists of Year_Month_Week_Program_ProductionLine.

Now the issue is that based on Help1 the record is assigned 2x because we don't have the Production Line information. But this means that the Sales record is duplicated ... as we can see in the first table in column L. However, I would need something like merging these two cells into one and thus assigning the Sales value only once and not twice. As we can see in the table below in column L.

I need all this to make a Power BI report, where when I filter by Program, the Sales values ​​will not be duplicated, but when filtering one and the other line that belongs to the given Program, it will show the same value.

Thank you very much for your help.

[v-shex-msft]

Hi @Cevaro,

If the default aggregated value not suitable for your requirement, you can consider writing measure formula with variable and summarize function to aggregate records based on current groups.

All the secrets of SUMMARIZE - SQLBI

Regards,

Xiaoxin Sheng

[Cevaro]

Hi, I'm trying to create it somehow for my case, but I can't.

Would you be able to help me compile such a Measure for the aforementioned problem, please?

Thank you very much