Measure - powerpivot flattened pivot table

[Djangom]

Hi All.
I have data that looks like this :


Id Category AA Category BB Category CC status Numbers
1 1 4 2 New 23
2 3 1 3 Old 34
3 1 5 5 Old 76
4 2 6 1 Old 12
5 2 1 2 Old 7

and is wondering if its possible to get the below buy using power pivot flattened pivot table:
Category Freq (count 1 or 2)
AA 2
BB 2
CC 3

Right now I can get the above result - but I have to do one more extra step to re-arrange the data before applying the flattened pivot table -

Id Category category list status Numbers
1 AA 1 New 23
2 AA 3 Old 34
3 AA 1 Old 76
4 AA 2 Old 12
5 AA 2 Old 7
1 BB 4 New 23
2 BB 1 Old 34
3 BB 5 Old 76
4 BB 6 Old 12
5 BB 1 Old 7
1 AA 2 New 23
2 AA 3 Old 34
3 AA 5 Old 76
4 AA 1 Old 12
5 AA 2 Old 7


Please let me know if this is doable - basically to avoid this extra ( re-arrange data) step.

Thanks in advance

 

[scottsen]

The unpivoted data is preferred, maybe you can use Power Query to do that for you?

I suspect it is possible to do your work without the unpivot, but I didn't actually understand what you are trying to calculate

you want the # of rows that has a 1 or a 2 in AA column (and similar for BB, CC columns?)

 

[Djangom]

Thanks Scott. Actually is the frequency of 1 or 2 in each category ( sorry the above count is wrong)..Should be :

Category
AA 4
BB 2
CC 3

Please let e know how to proceed in power query.

Thanks again

 

[scottsen]

So, if you have just a few categories, you could do this "directly". I would probably create a table of the unique categories, so you can drag them onto rows of your pivot table.

Then you end up with something (kinda ugly), like:
=IF(HASONEVALUE(Categories[Category]),
IF(Categories[Category] = "AA", [Count AA],
IF(Categories[Category] = "BB", [Count BB],
...

If you want to go down the power query route, I would... install it, import your data, then look for the "unpivot" option in the banner.

 

[Djangom]

Thanks Scott - that works perfect!!. I really appreciated.