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.
I am using a line visualization with multiple legend values. I want to show legends as a percentage of the total per month.
Now each legend value is displayed as a percentage of the whole.
For example, if the value for January is 2.78 % when I add a legend column which has values A, B, C the percentage is displayed as
A - 0,73 %
B- 1,71 %
C- 0,33 %, which is the percentage of the total
but I want to display it as
A- 26.4 %
B - 61.71 %
C- 11.89 %, which is the percentage of the total per month. each legend item should sum to ~100%
Is there a way to do this in a line chart?
Regards,
Hi @Anonymous,
Please share some dummy data with raw table structure, then we can do test coding formula on it.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
I am using a line chart to display the distinct count of customers with multiple legend values. I want to show the count of customers as a percentage of the total per month.
Now each legend value is displayed as a percentage of the whole.
For example, if January had a percentage of value 2.78% when I added a legend column that has A, B, C values, the percentage is displayed as
A - 0.73%
B- 1.71%
C- 0.33%, which is the percentage of the total, but I want to show it like
A- 26.4%
B - 61.71%
C- 11.89%, which is the percentage of the total per month. each legend item must add to 100%
Is there a way to do this on a line chart?
I also want to add another line chart, which sums up legend values A and B.
Is this possible to do?
Below is my sample data.
Regards,
Legend | Customers | YearMonth |
A | 1212144 | 201904 |
C | 8585899 | 201904 |
C | 5858989 | 201904 |
A | 9898877 | 201905 |
A | 8989988 | 201905 |
C | 8989999 | 201905 |
A | 9999921 | 201904 |
A | 2121212 | 201903 |
B | 2121212 | 201904 |
B | 1414121 | 201904 |
B | 2221111 | 201903 |
C | 4414141 | 201903 |
C | 2121212 | 201903 |
C | 7111212 | 201902 |
C | 7777721 | 201904 |
C | 8232323 | 201904 |
C | 3333212 | 201902 |
B | 2121233 | 201905 |
B | 1241414 | 201905 |
A | 1241414 | 201902 |
A | 1212121 | 201902 |
B | 4444455 | 201904 |
C | 2121255 | 201902 |
B | 4747777 | 201903 |
Hi @Anonymous,
Please check the below measure formula if it works on your side:
Measure =
DIVIDE (
CALCULATE (
COUNTA ( 'Table'[customers] ),
ALLSELECTED ( 'Table' ),
VALUES ( 'Table'[YearMonth] ),
VALUES ( 'Table'[Legend] )
),
CALCULATE ( COUNTA ( 'Table'[customers] ), ALLSELECTED ( 'Table' ) )
)
Regards,
Xiaoxin Sheng
Hi
I am tring to create a Line chart with following data. I need the output of line chart to look as per tabel 2. but it doesnt take % of COlumns - It considers % of grand total.
Fields are - product name - in col/ Axis. Month in row/ legend. COunt of customer id is used.
Appreciate your help.
Thank you for your reply!
I also want to add another line chart, which sums up legend values A and B.
Is this possible to do?
Hi @Anonymous,
Currently, you can't use the legend field and multiple value fields at the same time. Perhaps you can try to use R visual to manually plot the graph.
Regards,
Xiaoxin Sheng
@Anonymous , try like
divide([measure], allexcept(Table, Table[month], Table[category]))
oe
divide([measure], allexcept(Table, Table[category]))
Also refer https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |