Forum Discussion
Displaying data in Power Bi
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
4 Replies
Hi Anonymous,
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
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.Hi Anonymous,
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