Adding a certain field into my Table Visualization creates duplicated rows

[Funk-E-Guy]

I have the following tables:

• Date
• SKU Data
• SKU Strategy by Market
• Sales   (historic data only)
• Generated FCST   (projected future data only)
• Manual FCST   (past & future data)
• Market

And I have created the following measures / Calculated Fields:

• Measure in 'Sales': Total Sales in Period = SUM('Sales Table'[QTY])
• Measure in 'Manual FCST': Total FCST in Period = SUM('Manual FCST'[QTY])
• Calculated Field in 'Generated FCST': Combined Sales & Projection = 'Generated FCST'[QTY] + [Sales Within Period]
• Measure in 'Generated FCST': Total Sales & Projection in Period = SUM('Generated FCST'[Combined Sales & Projection])
• Measure in 'Generated FCST': FTA % = DIVIDE([Total Sales & Projection in Period], [Total FCST in Period])

With the above, I try to show a Table Visualization with the following fields:

• 'SKU Data'[SKU]
• 'Market'[Location]
• [Total Sales in Period]
• 'Generated FCST'[QTY]
• [Total Sales & Projection in Period]
• [Total FCST in Period]
• [FTA %]
• 'Sku Strategy by Market'[Status]

The above table seems to work perfectly IF I exclude the last field; 'Sku Strategy by Market'[Status]. As soon as I include it, it will create duplicate rows for every entry, one for each unique "Status". For example, instead of this:

SKU	Location	Total Sales in Period	'Generated FCST'[QTY]	Total Sales & Projection in Period	Total FCST in Period	FTA %
TestSKU123	US	200	1200	1400	1000	140%

I get this:

SKU	Location	Total Sales in Period	'Generated FCST'[QTY]	Total Sales & Projection in Period	Total FCST in Period	FTA %	Status
TestSKU123	US	200	1200	1400	1000	140%	Live
TestSKU123	US	200	1200	1400	1000	140%	Launch
TestSKU123	US	200	1200	1400	1000	140%	Discontinued

I have made sure that my Relationships are connected & active.

• Date is pulled into my Sales, Generated FCST, and Manual FCST
• SKU is pulled into my SKU Strategy, Sales, Generated FCST, Manual FCST
• Market is pulled into SKU Strategy, Sales, Generated FCST, Manual FCST

[Funk-E-Guy · Accepted solution]

Hi @Icey,

Sorry for not getting back to you more quickly. I took a second look at my relationships, and I'm really not quite sure what could be done differently, and I couldn't find time to replicate my model without sensitive data.

But I did find a solution, although I'm not sure if it would be considered best practice; I right-clicked "Status" in the field list, and selected "First". This correctly shows the singular status for each Market/SKU pair, which I find odd because the status is defined by the Market/SKU pair, so I'm still not quite sure why it was originally disregarding that association and just duplicating the rows (one for each unique Status).

[Icey]

Hi @Funk-E-Guy ,

This may be caused by the relationships among your tables.

Could you share us a dummy .pbix file for test? Please don't contain any real data or sensitive information. In addition, it is suggested to upload your file to OneDrive for Business and then paste the link here.

Best Regards,

Icey

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