Needed some ideas on manipulating report

Hello,

test_Subgroup.pbix (25.3 KB)
I have a question. I am trying to create a table that will have columns stacked underneath one another. My actual data is very big in size and is coming in from the server. I know that I could create the tables and stack them underneath one another, however, I have been asked to do it in a certain way. And, I have never worked with data manipulation to such circumstances before, so any kind of help would be appreciated.
I am attaching the file here. You will be able to see that I have 3 tables stacked underneath one another. I did try creating queries with conditional columns for each subgroup type and append them afterward. But, it would not work much since my dataset has over a million records. However, I am trying to create a table that would look more like this:

image

Hi @supergallagher25

I am not sure why you said this “It would not work much since my dataset has over a million records”. Power query can handle millions of rows without problem.

Try appending them, you can also create an index column to preserve the name of the original table.

Best,
DJ

Hi @supergallagher25,
I simply unpivoted the Product and Brands columns from your original table.
image

Then deleted the attribute column and renamed the values column to “SubGroup”.
image
and then created a simple countrows formula:
SubGroup Count = COUNTROWS(REF_Table_UNPIVOT)

Here is my result:
image

Attached PBIX test_Subgroup.pbix (32.1 KB)

I hope this helps.

2 Likes

Hi @supergallagher25, did the response provided by @JBright help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

He almost did, however, my count (even with distinct) is not showing the number it is supposed to afterward. Also, it takes off two of my other columns. I needed those for calculation and they just disappear.

@supergallagher25
I think we may need a better understanding of what you are trying to achieve here.

What @JBright did was correct to bring products, brands, and States into a single column. However, if we do that, then you are going to be counting your customers more than once (as you saw with the end result) - because each transaction now exists on the table three times - once each for the Brand, Product, and State. That is why your count no longer works.

Would a Matrix table be a better solution for you?

Thank you so much for that solution. I had created a different measure and it seems to work pretty well. However, all of the values are not showing up on my matrix table in the subgroup section, which would be the brand column from your table. When I bring in the measures in my table visual as columns, it takes off the devices, and only shows the Brands and their calculations in the measure.

Attached is my mockup with a Matrix table, I have also added a Count Measure (instead of just dragging customer ID in as a counted value) - I would suggest using measures whenever possible.

test_Subgroup (1).pbix (26.3 KB)

With a Matrix Visual, you add both your Brand and Product on the Rows area (the one at the top will be the primary group - in bold text), with your measure in the Values section.
image

and then to expand it out as I have, you need to click the forked arrow icon at the top of the visual:
image

1 Like

I figured it out. There was something wrong with my calculation. Thank you so much for your help.