Counting instances across 8 columns of data

[JaySanctuary]

Hi all, brand new to PowerBI and managed to get the majority of what I needed created at this point, however I'm now stuck with the mentioned. Each "Check type" has a different set of "deductions" but I'm wanting to display a table where if you were to filter the page to check type m1 for example, it would show a single list of the combined total of the deductions, so in the example below, it would show "M1Deduction6 - 2" and so on.

I did read about doing an unpivot but then wouldn't that create each row of data into 8 rows and then scew all other figures?

Thanks in advance for any help!

Adviser	Checktype	Outcome	Deduction type 1	Deduction type 2	Deduction type 3	Deduction type 4	Deduction type 5	Deduction type 6	Deduction type 7	Deduction type 8
Adam	M1	Pass	M1Deduction 6	M1Deduction 10
Dave	R4	Fail	R4Deduction 2
John	P2	Fail	P2Deduction 8	P2Deduction 6	P2Deduction 14	P2Deduction 2	P2Deduction 23	P2Deduction 24	P2Deduction 25	P2Deduction 26
Mike	R2	Fail	R2Deduction 7	R2Deduction 15	R2Deduction 2
Adam	M2	Pass	M2Deduction 4
Adam	M1	Pass	M1Deduction 10	M1Deduction 6	M1Deduction 7	M1Deduction 8	M1Deduction 10

[JaySanctuary]

Hi @rajendraongole1 , thankyou for the reply!

Sorry I may not have explained the expected result the best, I already have a check type filter set up on my dashboard so that part should be fine, but in terms of the table, if I was to filter to "M1", I'd like it to look like the below, with a count of the actual deduction, rather than the collumn header and not split by adviser. Then I was hoping with a drill down, if I was to click on "M1deduction10" for example, it would show the names of the advisers with this deduction.

Thanks again

Deductions	Count
M1Deduction 10	3
M1Deduction 6	2
M1Deduction 7	1
M1Deduction 8	1

[v-junyant-msft · Accepted solution]

Hi @JaySanctuary ,

You can try this:
First in the Power Query:
Select columns Adviser, Checktype and Outcome, Transform > Unpivot Columns > Unpivot Other Columns:

And the output is as below:

Click Close & Apply:

Use this DAX to create a measure:

Count = COUNT('Table (2)'[Value])

Put the column Value and the measure into the table visual, and in Filters on this visual > Value > Advanced filtering > is not empty:

Then the final output is as below:

Also you mentioned ‘Then I was hoping with a drill down, if I was to click on “M1deduction10” for example, it would show the names of the advisers with this deduction’. But drill down has to have hierarchical results to work (e.g. year, quarter, month, day, etc.), and your data structure doesn't suggest drill down, I suggest you use drill through:
On page2, create another table visual:

Still on page2, drag the Value field into the drill-through fields:

Then the final output is as below, right click M1deduction10:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @JaySanctuary - you can display a table showing the combined total of deductions for a specific "Checktype" when filtered

In query editor, i have performed unpivot other columns by selecting the first two adivser and checktype below is the output.

Once unpivot load the changes to desktop and create a measure for total deductions

Total Deductions = COUNTROWS('Dedcu')

Hope it work , if not please share the expected result 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!