Forum Discussion

clairethnguyen's avatar
clairethnguyen
Frequent Visitor
7 years ago
Solved

Year over year comparison

Hello,   I have a data set like this: Year Program name Has curriculum map use of results 2014 A yes yes 2014 B yes yes 2014 C no yes 2014 D no yes 2014 E yes y...
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi clairethnguyen,

     

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

     

     

    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

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

Hi clairethnguyen,

 

Do you want to get the output like this?

 

 

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.

 

 

Best Regards,

Cherry

clairethnguyen's avatar
clairethnguyen
Frequent Visitor
7 years ago

Hi Cherry v-piga-msft

 

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

 

Is there a way to do this? 

 

Thank you very much.

C

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi clairethnguyen,

     

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

     

     

    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

    • clairethnguyen's avatar
      clairethnguyen
      Frequent Visitor
      7 years ago

       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.