cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

EDITED: Stacked Bar chart by Year

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

 

ContactDateAlert
AJuly 2019Passive
AJanuary 2020Passive
AJuly 2020No reply
BJune 2019Passive
CApril 2019No reply
CNovember 2019No reply
CMay 2020No reply
DMarch 2020No reply

 

 

Data 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: EDITED: Stacked Bar chart by Year

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.

 

EDITED1.jpg

 

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

 

EDITED2.jpg

 

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] ) )
)

 

EDITED3.jpg

 

Edited4.jpg

 

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.

View solution in original post

4 REPLIES 4
Highlighted
Continued Contributor
Continued Contributor

Re: Stacked Bar chart by Year

@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.

Highlighted
Super User IX
Super User IX

Re: Stacked Bar chart by Year

@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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: EDITED: Stacked Bar chart by Year

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.

 

EDITED1.jpg

 

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

 

EDITED2.jpg

 

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] ) )
)

 

EDITED3.jpg

 

Edited4.jpg

 

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.

View solution in original post

Highlighted
Helper III
Helper III

Re: EDITED: Stacked Bar chart by Years

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?

ContactDateAlert
AJuly 2019Passive
AJanuary 2020Passive
AJuly 2020No reply
BJune 2019Passive
CApril 2019No reply
CNovember 2019No reply
CMay 2020No reply
DMarch 2020No reply
ENovember 2019Promoter
ESeptember 2020Promoter
FJuly 2019Promoter
FMarch 2020Promoter
GMay 2019No reply
GNovember 2019No reply
HMarch 2019Promoter
HSeptember 2019Promoter
ISeptember 2019Passive
IMarch 2020No reply
JMay 2019No reply
JJanuary 2020No reply
JJuly 2020No reply
KOctober 2019No reply
KAugust 2020No reply
WMay 2019Detractor
WSeptember 2019No reply
WAugust 2020No reply

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors