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

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. 

Capture.PNG

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.

Capture1.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Hi, @sheap069 

 

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

 

Table:

e1.png

 

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:

e2.png

 

Best Regards

Allan

 

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

2 REPLIES 2
Highlighted
Community Support
Community Support

Hi, @sheap069 

 

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

 

Table:

e1.png

 

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:

e2.png

 

Best Regards

Allan

 

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

Highlighted

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.

 

Capture.PNG

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors