Sum of rows

[Anonymous]

Hi

I have a simple question, as a newbie I couldn't find the answer.

In my xlsx file there are amongst other the following columns:

- Price

- Shipment

- Sales Price

-> Sales Price is Price + Shipment

- Should I remove the Sales Price from the imported table and then calculate it later in order to save 1 column?

- How can I display the product of each row of two columns in a visual table?

Thank you for your help.

[Anonymous · Accepted solution]

Dont know what was wrong but I created a measure with "measure = sum(a)+sum(b)" and now it's working. So it's solved.

[Icey]

Hi @Anonymous ,

This is by design. In Power BI Report View, it will only return the distinct rows content which every row value is the unique key in visuals. While it will show all rows as Data view ,once you drag the other different value fields which are from related table onto the visuals. 

For eample, an index column:

For more details, please check the attached .pbix file.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[Greg_Deckler]

@Anonymous - Couple schools of thought here. One is that if Sales Price has lots of unique values, you can save space in the data model by not including it and doing it as a measure instead. You will need this though for your grand total as you will likely have a measures total problem. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Otherwise, if you data model size is not a concern then I would just include the Sales Price column and call it a day. You could then use the default SUM aggregation on that column.

[Anonymous]

Thanks man, I appreciate.

[amitchandak]

@Anonymous , You can create a measure

sum(Shipment) -Sum(Price)

See if this can work

[Anonymous]

Hi, I guess you meant sum(Shipment) +Sum(Price) -> as I need  the sum of Shipment + Price and not minus.

However, this works partially. It sums all the same values in a column. But I need a list with every single calculation.

Example:

Correct in Excel:

A     B     C

1     2     3

0.5  2     2.5

0.5  2     2.5

0.5  2     2.5

6     3     9

(A+B = C for each row)

Now based on your solution I get this in Power BI:

A     B     C

1     2     3

0.5  6     7.5

6     3     9

How can I display the three times 0.5+2=2.5 as three single rows instead of one row?

[ryan_mayu]

@Anonymous 

in the power query editor, select all the columns and click remove Duplicates.