Forum Discussion
Flat File - "Repeating Rows"
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.
4 Replies
Hi Anonymous,
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
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?
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
