Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |