Pivot Table with multiple rows all having the same level hierarchy

Question

I have imported a bunch of data using PowerQuery into a single table and am building dashboard reporting. I have been using Pivot Tables to build my reports, which has worked fine so far.

However, I've come to a point though where I want to simply show the count of multiple columns (calculated fields). So I have column A,B,C,D, and want to show the count each of each. But, I don't want them to be subsets (or children) of one another, and I don't want to build a bunch of Pivot Tables (file is already getting pretty big, and I want them row by row for easy viewing). Any suggestions?

Also, I am using the "Columns" field already to show the counts by certain weeks (week one, week two, etc.).

Thanks,

-A

Thanks for the follow-up. Within PowerPivot, I have four calculated fields/columns that are True/False for each column. I want to know how many times each of those columns were marked "True" (I can rename the "True" field to distinguish between which field it's referencing). But I don't want four pivot tables. Right now I can only think of making four pivot tables, filtering out the false for each one, then hiding the rows so the "True" values stack on top of one another. If I put all the four fields together in the same Pivot, the three below the first become subsets. I don't want subsets, just occurrence counts.

Does this help provide clarification?

Have you tried using a calculated field for this? I think it will do what is needed. — Byron Wall, Jun 08 '15 at 19:36
Could you add an example table to your question showing your initial column/row setup and the desired result? That would probably help a lot to understand your question. — Sascha Rambeaud, Jun 09 '15 at 15:08

score 1 · Accepted Answer · answered Jun 22 '15 at 08:52

1

If I understand you correctly, here's an example that shows what you're trying to achieve:

Simple example of true/false count using pets

The table on the left has the TRUE/FALSE entries and the PivotTable on the right just shows the number of true items in each of those columns.

The format of the DAX measure to produce these count totals is:

[Count of A]=CALCULATE(COUNTROWS(PetFacts),PetFacts[A]=TRUE)

(Apologies to any parrot owners who may get upset that I have inadvertently re-classified their pets as cold-blooded!)

answered Jun 22 '15 at 08:52

Gordon K

824
1
8
19

This is exactly what I needed. Thank you! – MarkO Jun 24 '15 at 16:19
Is there anyway to do that without having to create a new measure for every column? – Vinicius Zolin De Jesus Dec 10 '20 at 07:45
Yes. You can unpivot the data to produce a table with a 'pet' column, a 'category' column and a 'value' column and produce your pivot table from that. – Gordon K Dec 10 '20 at 09:25

Pivot Table with multiple rows all having the same level hierarchy

1 Answers1