How to plot line chart with percentage by category per month

[powerbidu]

Hi all, I have the below data set:

Months	Portfolio	Status
Jan	1	A
Jan	2	B
Jan	3	A
Jan	4	A
Jan	5	B
Jan	6	A
Feb	1	B
Feb	2	B
Feb	3	B
Feb	8	B
Feb	9	A
Mar	3	A
Mar	4	A
Mar	5	B
Mar	8	B
Mar	9	B

When I plot the data into a line chart, values are shown by count of portfolios across different categories in each month.

But I would like to show the values as the percentage of portfolios across different categories of each month's total (i.e. each month would sum up to 100%). In which case, the output would be something like the below, in line chart form:

Months	Portfolio	Status	%
Jan	4	A	67%
Jan	2	B	33%
Feb	1	A	20%
Feb	4	B	80%

I referenced to this prior link but the solution did not seem to work for me:

https://community.fabric.microsoft.com/t5/Desktop/Line-chart-with-percentage-per-month-by-category/m...

Thank you.

[v-sgandrathi]

Hello @powerbidu 
Thanks for connecting with the Microsoft Fabric Community Forum.

To resolve your problem in Power BI, start by loading your data. Open Power BI Desktop, then go to Get Data > Enter Data. Paste your sample data into the table, name it PortfolioData(In sample data i have taken Table name as Table2), and click Load.

Next, switch to the Modeling tab and create two measures. The first measure, TotalPortfolios, counts the total number of rows in your dataset. The second measure, Percentage, calculates the percentage of portfolios by Status for each month. It uses the DIVIDE function together with CALCULATE and ALLEXCEPT to ensure that the count is evaluated per month, and then multiplies the result by 100 to express it as a percentage.

Below are the Measures:

TotalPortfolios = COUNTROWS(PortfolioData)

Percentage = DIVIDE(

    COUNTROWS(PortfolioData),

    CALCULATE(

        COUNTROWS(PortfolioData),

        ALLEXCEPT(PortfolioData, PortfolioData[Months])

    ),

    0

) * 100

Now, go to the Report view and add a Line Chart visual. Assign the Months field to the X-axis, the Percentage measure to the Y-axis, and the Status field to the Legend.

 Finally, format the Y-axis to display values as percentages. Your chart should now correctly show two lines, with percentages that add up to 100% per month (for instance, January might display 60% for Status A and 40% for Status B).

Additionally, I have included the PBIX file that I created using the provided sample data. Kindly review it and confirm whether it aligns with your expectations.

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Regards,
Sahasra.

[johnt75 · Accepted solution]

You can create a couple of measures like

Num Portfolios = COUNTROWS( 'Table' )

Status % = 
VAR CurrentNum = [Num Portfolios]
VAR Total = CALCULATE(
    [Num Portfolios],
    ALLSELECTED( 'Table'[Status] )
)
VAR Result = DIVIDE( CurrentNum, Total )
RETURN Result