EDITED: Stacked Bar chart by Year

[Vandergledison]

EDITED: for the sake of clarity i have to EDIT this post:

So, what i need help is to get a Chart containing the Contac count of (Passive, No Reply, promoter, etc) by Year (2019 and 2020)

the problem is: in 2020 for example, if an "Alert" reply was "Passive" in the begining of the year and "no Reply" later in the same year, it should count only for "Passive", The "No Reply" should count only if there is only "No Reply", any other answer in the same year, it should take that input (passive, promoter, etc).

basically i need to collect only the latest answers that gave some result, the ones that have only "No reply" should that acount for the No replay bucket.

i hope is a bit more clearer.

thanks a lot

Contact	Date	Alert
A	July 2019	Passive
A	January 2020	Passive
A	July 2020	No reply
B	June 2019	Passive
C	April 2019	No reply
C	November 2019	No reply
C	May 2020	No reply
D	March 2020	No reply

Data 

[v-zhenbw-msft · Accepted solution]

Hi @Vandergledison ,

We can add an index column and pivot it, then create four measures to meet your requirement.

1. Add a year column and an index column in Power Query Editor.

2. Then remove the Date column and pivot the index.

3. At last, we need to create four measures and put them in table visual.

Passive measrue = 
CALCULATE (
    COUNT ( 'Table (2)'[Contact] ),
    NOT ( ISBLANK ( 'Table (2)'[Passive] ) )
)

No reply measrue = 
CALCULATE (
    COUNT ( 'Table (2)'[Contact] ),
    ISBLANK ( 'Table (2)'[Passive] ),
    ISBLANK ( 'Table (2)'[Promoter] ),
    ISBLANK ( 'Table (2)'[Detractor] ),
    NOT ( ISBLANK ( 'Table (2)'[No reply] ) )
)

Promoter measrue = 
CALCULATE (
    COUNT ( 'Table (2)'[Contact] ),
    NOT ( ISBLANK ( 'Table (2)'[Promoter] ) )
)

Detractor measrue = 
CALCULATE (
    COUNT ( 'Table (2)'[Contact] ),
    NOT ( ISBLANK ( 'Table (2)'[Detractor] ) )
)

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[Vandergledison]

hi . Thanks a lot, this works to a certain extend.

my problem is that the final goal is to cluster the responses considering their answers. For example, if in 2019 the answer was "no replay" and in 2020 was " promoter" than i should be able to count this contact as " no reply/promoter" or "no reply/no reply" and so far. i think i need to split the years:

it is possible to pivot the table containing Text? to transfer the 2019 and 2020 in two separate columns containing the corresponding Alert answers?

Contact	Date	Alert
A	July 2019	Passive
A	January 2020	Passive
A	July 2020	No reply
B	June 2019	Passive
C	April 2019	No reply
C	November 2019	No reply
C	May 2020	No reply
D	March 2020	No reply
E	November 2019	Promoter
E	September 2020	Promoter
F	July 2019	Promoter
F	March 2020	Promoter
G	May 2019	No reply
G	November 2019	No reply
H	March 2019	Promoter
H	September 2019	Promoter
I	September 2019	Passive
I	March 2020	No reply
J	May 2019	No reply
J	January 2020	No reply
J	July 2020	No reply
K	October 2019	No reply
K	August 2020	No reply
W	May 2019	Detractor
W	September 2019	No reply
W	August 2020	No reply

[amitchandak]

@Vandergledison , Not able to access your file. Please share again

Looking at this you might have unpivot the measures. and take year or contact as a legend.

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[mhossain]

@Vandergledison 

Structure your data:

--Unpivot/multirow your data in a way so that, you have Year single column having values 2019, 2020

Now you creat measure for count.

Create stake chart, drag year to x axis, color to legend and count measure to the value section.