Forum Discussion
Kudo KingpinSum Unique Rows after Unpivot
I have a table with unique rows. I had to unpivot the table based on a column to get a value I was looking for. The problem is, after I unpivot the table, all of the other measures I would try to make get blown out of proportion because of the unpivot.
Basically, I need to sum the values of a column BEFORE the unpivot. But, I still need to do the unpivot.
Any ideas??
I’m not sure the visual/table you want now. If you just want to count the # Seats column for each unique row based on the ID column. Just create a new table with following formula.
Best Regards,
Herbert
22 Replies
Can you post the PBIX file? If we can see the structure of your data, we may be able to help.
SInce you mention unpivoting, I'm wondering if first we can use the Power Query Formula Language (M) to unpivot the data to get a desired column, and then stitch it back to the original pivoted table. Again, very difficult to know without seeing the table.
Best,
~ Chris
BKnechtHi Chris,
Unfortunately, I cannot share the dataset as it contains confidential information about our business - and I know it's almost impossible to answer these types of questions without the data model. I think the best best option may be to try the Power Query option - I didn't think of that before. I'll let you know if it works. Thanks again for your advice.
v-haibl-msftMicrosoft Employee
We need to do unpivot in Query Editor, but the measure results cannot be used there. Unpivot will change the table structure, so the measure which created before unpivot will be affected. According to your description, maybe you can first try to use Power Query to get the expected result and then do unpivot in Query Editor as ChrisHaas said.
Best Regards,
Herbert
Did you end up finding a solution?
You can always randomize some data and send us a dummy file, stripping out any confidential names or values. We just need the table(s) in the same structure.
- BKnecht
Anonymous wrote:BKnecht easiest way to share a problem query is to use a fake data source. This site makes it pretty easy to create dummy test data for such cases. Make something that looks like the important parts of your source data and run it through the same query steps to show us what you're attempting.
Anonymous wrote:BKnecht easiest way to share a problem query is to use a fake data source. This site makes it pretty easy to create dummy test data for such cases. Make something that looks like the important parts of your source data and run it through the same query steps to show us what you're attempting.
AnonymousAnonymous - Unfortunately I did not find a solution, I just brought in the table a second time (it isn't a big table). It's not ideal because now I have to update 2 tables every month, but I had to get this dashboard out to leadership.
That mock data website is great - thanks for sharing. I've attached a PBIX file of what the table and query steps look like. What I'm trying to do is count the # Seats column for each unique row based on the ID column. So for example, the count of seats for ID 1 would be 20, and for 2 it would be 89, etc.
Again, the problem is now that I've unpivoted the data so there are multiple rows for each ID. Any suggestions? Thank you for the help!
Stupid question - how do I attach the pbix file to this post? I don't see a file attachment option anywhere.
- v-haibl-msft
Anonymous
Try to create a measure with following formula.
Measure = CALCULATE ( DISTINCTCOUNT ( Table1[ID] ), ALLEXCEPT ( Table1, Table1[Month] ) )Best Regards,
Herbert
