Data Model Pivot Table Question

[coatsy35]

Hi,

I have a pivot table that is generated from a data model comprising of two tables. The first table (Targets) has a column with the sales rep names in it, then a column for the target qty of each particular item they have been tasked with selling - there are multiple of these columns. The second table (Sales) has the first column of reps name, then price sold, make and model columns. The tables are linked by name and date. I have a measure set to count how many of each make and model, which gives me the pivot table below if I do not fill in the target in my first table. However, when I fill in target column in the Targets table to populate the target column un the Pivot table, the target value is added to every row of the pivot (see second image below) and not just the name row (scrubbed out in black) - which makes it look like every item on the list has a target. I know this is standard behaviour, but is there any way to only show the value in the name row and not in model rows beneath? Example at the very bottom of this post of what I want. Any help appreciated.

Ideally want it to look like below:

 

[Matt Allington]

It appears there are many issues here. I’m not really sure where to start. You mention you’ve joined the tables by name and date, but there’s no mention of any date columns in any of the tables. the semantic model is the foundation of everything else you do. You need to get that right. I suggest you do some reading To build your knowledge first.

What is a Power BI Data Model?

If you don’t know (or are not 100% sure) what a Power BI Data Model is, you have come to the right place. I see a lot of people confused as to this term “data model”, and frankly I don’t blame anyone for being confused – it’s a foreign concept [...]Read More »

exceleratorbi.com.au

The Optimal Shape for Power BI Data

It is common for self taught Power Pivot users to get the shape of their data all wrong. This article describes the optimal way to do it.

exceleratorbi.com.au

then post images of your tables and relationships. It is unlikely the way they‘re structured now is not correct.

 

[coatsy35]

It appears there are many issues here. I’m not really sure where to start. You mention you’ve joined the tables by name and date, but there’s no mention of any date columns in any of the tables. the semantic model is the foundation of everything else you do. You need to get that right. I suggest you do some reading To build your knowledge first.

What is a Power BI Data Model?

If you don’t know (or are not 100% sure) what a Power BI Data Model is, you have come to the right place. I see a lot of people confused as to this term “data model”, and frankly I don’t blame anyone for being confused – it’s a foreign concept [...]Read More »

exceleratorbi.com.au

The Optimal Shape for Power BI Data

It is common for self taught Power Pivot users to get the shape of their data all wrong. This article describes the optimal way to do it.

exceleratorbi.com.au

then post images of your tables and relationships. It is unlikely the way they‘re structured now is not correct.

They also have a date column. I missed that bit out when I was over simplifying it