All rows and total of the rows showing same values in power BI

4
votes

I am trying to show "Some Column Name" against "Total Amount" in Power BI.

I am expecting the following results:

enter image description here

But instead it's showing me the following results:

enter image description here

The current data type is "fixed decimal number" I tried changing it to "Decimal number" or "Whole Number" but it did not work.

Any suggestions?

2
powerbipowerbi-desktop
Is your data coming from more than one table? – Aldert
Is the row total a measure or a calculated column or is it a straight pull from a data source? – Muskie
@Aldert yes, between those two tables there is a common table from which the data is coming. Example, TradeID and AmountID are foreign key to some other table. – Paras

2 Answers

2
votes

This is almost certainly the result of a many-to-many relationship in Power BI. In Power BI if there are connections between two (or more) tables that evaluates to a many-to-many relationship then Power BI is unable to make a distinction between any two rows and instead will project the same value for any given row placed in a visual.

Here's an easy way to visualize this:

Table 1              Table 2
Product | ID    AmountSold | ID
Widget    1         10       1
Smidget   1         20       2
Gidget    2          5       1

When you join these Power BI can't tell the difference in Sales between Widget and Smidget because they have the same ID. As far as Power BI knows Widget could have sold 0 and Smidget sold a total 15, or Widget sold a total 5 and Smidget sold a total 10, etc.

As a result of this many-to-many relationship Power BI panics and evaluates them to be the same result because it can't determine what is right. Widget and Smidget both sold a total of 15:

        Visual
 Product | AmountSold
 Widget       15
 Smidget      15
 Gidget       20

You're experiencing the same issue in your data model, although it is undoubtedly a much more complex relationship than the one I just laid out. You need to go back and determine what table(s) are experiencing a many-to-many relationship and fix the issue. Based on the information you've provided it can't be done from here.

2
votes

The reason why you have a single repeating value is because you did not create a relationship in powerbi model. If for example you have two tables Sales and Staff and you want to sum all staffs sales.

enter image description here

if no relationship created between table in power BI

enter image description here

The output will look like this

enter image description here

However when a relationship is created between the tables enter image description here

The output will be correct

enter image description here