cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Sum 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??

1 ACCEPTED SOLUTION

@BKnecht

 

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.

 

Sum Unique Rows after Unpivot_1.jpg

 

Best Regards,

Herbert

View solution in original post

22 REPLIES 22
Anonymous
Not applicable

@BKnecht

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.

Capture.PNG

 

I have the same issue. I need to get counts of several columns like above table 1. also i need to unpivot like table 2. but need to get the actual count of rows (before unpivoting count). how to get that? 

 

Thanks. 

 

@Dinu

 

Try to create a measure with following formula.

 

Measure =
CALCULATE (
    DISTINCTCOUNT ( Table1[ID] ),
    ALLEXCEPT ( Table1, Table1[Month] )
)

 

Best Regards,

Herbert

@v-haibl-msft

 

It worked for a Card, only for showing the count. thnks alot. but If we need to show a count/or a sum of columns by a different data  legand  by a bar chart, it won't react. please find the attached pics. It works for a card.It works for a card.it wont work for this type of chartit wont work for this type of chart

Community Champion
Community Champion

@Dinu the ALLEXCEPT function means that any other filtering context will be ignored except whatever columns are listed there. The formula from @v-haibl-msft only lists the Month column. If you want to chart this against some other columns such as Category, you'll have to add those to the list.





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

Proud to be a Super User!




@KHorseman @v-haibl-msft Yeah when I added those columns to the measure the chart will comes as expected. but it wont react for the Month Filter. The reason I unpivoted month columns is to add a filter by month. Please give me a solution.  Thanks

Community Champion
Community Champion

@Dinu I gave you the solution . Add the caregory column to the ALLEXCEPT function. Right after the month column. Type a comma, a space, and the name of the category column. Hit Enter.





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

Proud to be a Super User!




@KHorseman yeah But the chart won't react for the Month filter. I need to filter that chart by unpivoted colum(Month), thats why I wanted the unpivoting. 

@Dinu

 

Please try the solution provided by KHorseman. If it still not works, please provide the sample table which includes Assignment Category and Type of Assignment.

 

Best Regards,

Herbert

@v-haibl-msft It didn't work. I've uploaded the sample excel and the BI file. Please go through the following link. I need to filter by both month column( appear after unpivoting) and the Assignment category column. Please help. 

 

https://drive.google.com/open?id=0B8t7sq0ueJftSGJ2TFJUeElhNzA

 

 

 

Thanks and regards,

Dinu

@Dinu

 

Actually it can be filtered by Month with current formula. We cannot see any difference by selecting different months in slicer, because there are always three Operational, one Financial and one IT in each month. So we always get 3-1-1 no matter we select any month. If you add Time column into Value field, we can see the changes.

 

Sum Unique Rows after Unpivot_1.jpgSum Unique Rows after Unpivot_2.jpg 

 

Best Regards,

Herbert

@v-haibl-msft yeah it's working. I've had selected the wrong column. Sorry for bothering you. Thnaks alot for the DAX @v-haibl-msft. You saved me 🙂

Community Champion
Community Champion

Yes it will. Leave the month column as it is. Add the category column.





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

Proud to be a Super User!




@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.





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

Proud to be a Super User!





@KHorseman 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.



@KHorseman 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@KHorseman - 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.

 

 

@BKnecht I don't think there is a file upload option here. You'll have to put it on dropbox or google drive or something and give us a link.





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

Proud to be a Super User!




Thought that might be the case - here you are. Thank you again! Let me know if you have any trouble accessing the file.

 

https://www.dropbox.com/sh/wwoc4x976bpp7tj/AAA0WdqbnqYfwNXCAkdS9NCba?dl=0

@BKnecht

 

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.

 

Sum Unique Rows after Unpivot_1.jpg

 

Best Regards,

Herbert

View solution in original post

That is so much easier than what I was trying to do, and it works perfectly. Have never created a table with that funciton before. Thank you Herbert - you saved me from updating another File every month. Very much appreciated!

Microsoft
Microsoft

@BKnecht

 

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors