Create Data Structure for Histogram

[qwertzuiop]

Hello dear Power BI Community

Following problem to solve:

Lets assume this table:

Check_ID	Person	Rating
1	A	good
2	A	bad
3	A	bad
4	B	bad
5	B	good
6	C	bad
7	C	good
8	C	bad
9	C	bad
10	D	bad
11	D	good
12	E	good
13	E	bad
14	E	good

I would like to graphically show a distribution of the number of people in relation to the number of "Bad" ratings given.

In summary, the following data structure would have to be read out for this purpose

GOAL:

Num_of_Rating_Bad	Num_of_Persons
1	3
2	1
3	1

1x "Bad" rating was given by three people (B,D,E)

2x "Bad" rating was given by one people (A)

3x "Bad" rating was given by one people (C)

Finally, I would like to create a histogram from it.

In this case it would look like this whre x-axis is the num of persons any y-axis the num of bad ratings given.

Thank you very much for your contribution.

Cheers

qwertzuiop

[barritown]

Hi @qwertzuiop,

Try creating a new table using the query below.

And in text format for convenience:

Table = 
VAR tbl = SUMMARIZE ( data, data[Person], "Num_of_Rating_Bad", COUNTX ( FILTER ( data, data[Rating] = "bad"), data[Rating] ))
RETURN SUMMARIZE ( tbl, [Num_of_Rating_Bad], 
                   "Num_of_Persons", 
                   VAR CurrentValue = [Num_of_Rating_Bad]
                   RETURN COUNTX ( FILTER ( tbl, [Num_of_Rating_Bad] = CurrentValue ), [Person] ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[qwertzuiop]

Hello @barritown 

Thank you very much for your help.Works so far perfect ‌‌

But let me add another complexity.

Excuse me, i did not think of having to consider this as well

What if the ratings come from two surveys (Survey ABC / Survey DEF)

Check_ID	Person	Rating	Survey
1	A	good	DEF
2	A	bad	ABC
3	A	bad	...
4	B	bad
5	B	good
6	C	bad
7	C	good
8	C	bad
9	C	bad
10	D	bad
11	D	good
12	E	good
13	E	bad
14	E	good

How do I have to adjust the code you gave me to consider just "Survey ABC" for example?

This would be so big, if you could sovle this as well.

Cheers

qwerzuiop

[barritown · Accepted solution]

In this case, you'll need to modify the solution like that:

Table = 
VAR tbl = SUMMARIZE ( data, data[Person], "Num_of_Rating_Bad", COUNTX ( CALCULATETABLE ( data, data[Rating] = "bad", data[Survey] = "ABC" ), data[Rating] ) )
RETURN SUMMARIZE ( tbl, [Num_of_Rating_Bad], 
                   "Num_of_Persons", 
                   VAR CurrentValue = [Num_of_Rating_Bad]
                   RETURN COUNTX ( FILTER ( tbl, [Num_of_Rating_Bad] = CurrentValue ), [Person] ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian