Flat File - "Repeating Rows"

[irnm8dn]

I have a flat file that due to the hierarchical nature of the information has "repeating rows" in a category type.  This is causing my category sales to be grossly incorrect.

I need to figure out how to sum the distinct rows of the category without giving up the other granularity elsewhere in the report.

For instance, the category that I need to sum may be "Tomato" which repeats in every row, but the granularity elsewhere in the report includes the tomato type.  i.e. Plum, Roma, Cherry, Beefsteak

I need to sum the total sales of "tomatoes" as well as an individual sum for each "tomato type".

Thanks in advance.

[MFelix]

Hi @irnm8dn,

I suppose you have your information in two distinct columns, one with the Tomato and another with the tomato type. Using your Category/Type in the axis, legend ...  and adding the sum, average of sales in the values.

Example:

Regards,

MFelix

[irnm8dn]

Thanks for the reply @MFelix

Indeed, the axis can show me the distinction and aggregate the amount sold of each "type".  

The sample dataset below is a more representative of my challenge.  In the transaction amount you'll see $1,509 "repeated" and what I want to express is that the Total Sale is $1,509.  (PowerBI however is summing each row).   I need to manage the dataset so that I can show "$1509" sale of Tomatos, and still be able to show how much of each kind of tomato.)  Now imagine it with more begetables in the mix.

Does that help?

[MFelix]

Hi @irnm8dn,

Use the Unit price column for your graphs/measures, since the sum of the unit prices is the same as the transaction amount it will give you the same result.

Regards,

MFelix

[fhill]

Do you have another column such as 'Transaction ID' that also duplciates matching Transactions Total?  Like i've sampled below?  If so, I recommend duplicating then flattening your data into two different tables.  

One will have 1 row per TransID with the TransTotal (and anything else that is the SAME for all rows on the Transaction?).  You can easily do this in PowerBI Query Editor by right clicking your table and 'Duplciate'.  Next delete the columns that have innter transaction details, then tell PowerBI to remove Duplicates to flatten your data.  

The 2nd (original table) remove the columns no longer needed that are in your new flattened table.  Link the two tables back together by TrandID, and you will have a MUCH easier time working with your data and visuals!  Pull from the TLD (Transaction Level Detail) table when neded on that level, and pull from the Summary Level when needing summary data.

Because the data is still linked together, you can stll compare elements on both levels more easily as well.

TransID	Veg	Type	TransTotal	UnitPrice
18	Tomato	Cherry	1100	250
18	Tomato	Cherry	1100	350
18	Tomato	Plum	1100	300
18	Tomato	Plum	1100	200
19	Tomato	Cherry	600	50
19	Tomato	Cherry	600	100
19	Tomato	Plum	600	300
19	Tomato	Plum	600	150