Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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 4
MFelix
Super User
Super User

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:

 

TOmato.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

 

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?

 

Capture.PNG

Hi @Anonymous,

 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

 

TransIDVegTypeTransTotalUnitPrice
18TomatoCherry1100250
18TomatoCherry1100350
18TomatoPlum1100300
18TomatoPlum1100200
19TomatoCherry60050
19TomatoCherry600100
19TomatoPlum600300
19TomatoPlum600150



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.