Forum Discussion

sheap069's avatar
sheap069
Icon for Helper III rankHelper III
6 years ago
Solved

Get Column Percentage and Line Chart Values as Percentage

Hello,

 

I have 25 regions and I'm currently displaying the count of applications made to each region based on time in a line chart, as shown in the following image. 

This is working the way I want it to. However, I also want to show the percentage of applications for each region for each month in the chart. I only know how to get the Percentage of Grand total, but I want the percentage for each region to be a column percentage on the chart (adding to 100%). For example, I want the percentage of applications for Region 1 in February 2019 to be out of all applications that were in February 2019, not out of all applications ever submitted. 

 

This is the data I have in my line chart and the structure.

 

Thank you very much for helping with a solution for this.

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, sheap069 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

     

    Table:

     

    You may create a calculated column and two measures as below.

    Calculated column:
YearMonth = FORMAT('Table'[Date],"yyyy-mm")

Measure:
Percentage for each region = 
DIVIDE(
    SUM('Table'[Value]),
    CALCULATE(
        SUM('Table'[Value]),
        ALLEXCEPT('Table','Table'[Region])
    )
)

Percentage of applications for each region for each month = 
DIVIDE(
    SUM('Table'[Value]),
    CALCULATE(
        SUM('Table'[Value]),
        ALLEXCEPT('Table','Table'[Region],'Table'[YearMonth])
    )
)

     

    Result:

     

    Best Regards

    Allan

     

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

2 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, sheap069 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

     

    Table:

     

    You may create a calculated column and two measures as below.

    Calculated column:
YearMonth = FORMAT('Table'[Date],"yyyy-mm")

Measure:
Percentage for each region = 
DIVIDE(
    SUM('Table'[Value]),
    CALCULATE(
        SUM('Table'[Value]),
        ALLEXCEPT('Table','Table'[Region])
    )
)

Percentage of applications for each region for each month = 
DIVIDE(
    SUM('Table'[Value]),
    CALCULATE(
        SUM('Table'[Value]),
        ALLEXCEPT('Table','Table'[Region],'Table'[YearMonth])
    )
)

     

    Result:

     

    Best Regards

    Allan

     

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

    • sheap069's avatar
      sheap069
      Icon for Helper III rankHelper III
      6 years ago

      Hi v-alq-msft 

       

      Thank you very much for this, it works!. If I want to repeat this with years and days, I assume that I would repeat the same logic. I'm also looking to add the percentages of applications for year, day of the month (where I will have a calculated column) and the day of the week.

       

      Thank you again!

       

      Edit: I've actually tried with Day of Week but this doesn't seem to be working. Is it because I would need this to be a calculated column as well? I currently have it as a Text Column extracted from the Application Date, Sunday-Saturday.