Reply
Frequent Visitor
Posts: 9
Registered: ‎10-02-2018
Accepted Solution

Year over year comparison

Hello,

 

I have a data set like this:

YearProgram nameHas curriculum mapuse of results
2014Ayesyes
2014Byesyes
2014Cnoyes
2014Dnoyes
2014Eyesyes
2014Fno reportno report
2015Ayesyes
2015Bnoyes
2015Cnoyes
2015Dnoyes
2015Enoyes

 

 

I'm struggling with creating a bar chart to compare the percentage of programs with curriculum map/SLOs each year (so only count yeses in column 3 and 4). Right now when I drag "Year" to Axis and "curriculum map" to "Value", the percentage is calculated over the total number of yeses in the whole table and filter doesn't seem to work. Please help. I am new to Power BI and not familiar with DAX.

 

Thanks a lot!

 


Accepted Solutions
Community Support Team
Posts: 2,965
Registered: ‎02-06-2018

Re: Year over year comparison

Hi @clairethnguyen,

 

For your desired output, it seems that you want to get the output like this.

 

 

bar chart.PNG

If you want to get the output above, please follow the steps below.

 

1. Unpivot Has curriculum map and use of results columns in Query Editor.

2. Create the measures below.

yes = CALCULATE(COUNTROWS('Table2'),FILTER('Table2','Table2'[Value]="yes"))

totalrows for each year = CALCULATE(COUNT(Table2[Program name]),ALLEXCEPT(Table2,Table2[Year]))

percent = [yes]/ [totalrows for each year]

3. Create the bar chart.

 

More details, you could refer to the attachment.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Attachment

All Replies
Community Support Team
Posts: 2,965
Registered: ‎02-06-2018

Re: Year over year comparison

Hi @clairethnguyen,

 

I'm not clear about your output.

 

Do you want to have the output like below highlight in red?

 

bar chart.png

 

If it is, you could create the measures below.

 

count_product_each_year = CALCULATE(COUNTROWS('Table1'),ALLEXCEPT(Table1,Table1[Year]))

yes = CALCULATE(COUNT(Table1[Has curriculum map]),FILTER(ALLEXCEPT(Table1,Table1[Year]),'Table1'[Has curriculum map]="yes"))

Percent= [yes]/[count_product_each_year]

 

If you still need help, please share your desired output.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 9
Registered: ‎10-02-2018

Re: Year over year comparison

Hi Cherry @v-piga-msft

 

Thank you so much for your answer. Yes, it is what I want but I also want to put other categories into the same chart (in this case both column 3 and column 4 data for each year: in 2014: x % of the programs has a curriculum map, y% of the program use the results). Is it possible? What should I do if it is? 

 

Thanks,

Claire

Community Support Team
Posts: 2,965
Registered: ‎02-06-2018

Re: Year over year comparison

Hi @clairethnguyen,

 

Do you want to get the output like this?

 

result.png

 

If it is, please create another two measures like below.

 

Has curriculum map_yes =
CALCULATE (
    COUNT ( Table1[Has curriculum map] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Year] ),
        'Table1'[Has curriculum map] = "yes"
    )
)

Has curriculum map_Percent = [Has curriculum map_yes]/[count_product_each_year]

Then create the bar chart like blow.

 

bar chart.PNG

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 9
Registered: ‎10-02-2018

Re: Year over year comparison

Hi Cherry @v-piga-msft

 

I actually want to see how the values change over the year. It's similar to this report:

 

Capture4.PNG

Is there a way to do this? 

 

Thank you very much.

C

Community Support Team
Posts: 2,965
Registered: ‎02-06-2018

Re: Year over year comparison

Hi @clairethnguyen,

 

For your desired output, it seems that you want to get the output like this.

 

 

bar chart.PNG

If you want to get the output above, please follow the steps below.

 

1. Unpivot Has curriculum map and use of results columns in Query Editor.

2. Create the measures below.

yes = CALCULATE(COUNTROWS('Table2'),FILTER('Table2','Table2'[Value]="yes"))

totalrows for each year = CALCULATE(COUNT(Table2[Program name]),ALLEXCEPT(Table2,Table2[Year]))

percent = [yes]/ [totalrows for each year]

3. Create the bar chart.

 

More details, you could refer to the attachment.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Attachment
Highlighted
Frequent Visitor
Posts: 9
Registered: ‎10-02-2018

Re: Year over year comparison

 Hi Cherry @v-piga-msft

 

Thanks a bunch for your help. This is what I want. I just dragged "Attribute" into the axis. I guess now I need to figure out how to make it work for my complete data set.

Capture.PNG