How to calculate and show the 100% percentage of each category in each month

[dnguyen67]

Hi all,

How can I calculate the measure to show the percentage of each category with total 100% each month, not 100% over the time period?

For example, here is the chart with top 5 categories

 when I convert the data into the percentage, they shows the percentage of each category as 100% total over all time, but I want to show percentage of total in each month.

Yes, I can use 100% stacked column chart, but I need the line chart in this case. Is it possible? even I have to do some extra measure fields or columns

Thanks,

[Anonymous · Accepted solution]

Hi @dnguyen67 ,

I created some dummy data based on your screenshots and made a solution.

Sample data:

I reproduced your outputs.

Now here's my solution.

1.Create a month column, a year column and a sort column.

Month = FORMAT([Date],"MMM")

Year = YEAR([Date])

sort = MONTH([Date])

2.Sort the month column by the sort column.

3.Create a measure to calculate the percentage by category per month.

Percentage =
VAR _sum =
    SUM ( 'Table'[Value] )
VAR _totalpermonth =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Year] = MAX ( 'Table'[Year] )
                && [Month] = MAX ( 'Table'[Month] )
        )
    )
RETURN
    DIVIDE ( _sum, _totalpermonth )

4.Here's the expected output.

You can check more details from my attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This one helped my use case. I have had a similar problem where I was calculating the percent of the total per category (stream), where I wanted each category (each stream) to maintain 100% and its products to be a percent of the total per stream.

[Ashish_Mathur]

Hi,

Try this measure

=divide([Total sales],calculate([Total sales],allselected(Data[Category])))

If this does not work, then share the link from where i can download your PBI file.