Get Column Percentage and Line Chart Values as Percentage

[sheap069]

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.

[v-alq-msft · Accepted solution]

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]

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.