cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MetricsBI
Frequent Visitor

Make Months add up to 100%

Hello,

 

I am trying to figure out how to make each of my months add up to 100%. For example I want January to show 100% since everything was completed on time. Please see below.

 

MetricsBI_0-1623346923658.png

 

1 ACCEPTED SOLUTION

Hi @MetricsBI ,

You can adjust the measure like this:

percent =
CALCULATE (
    COUNT ( 'Table'[Condition] ),
    FILTER (
        'Table',
        'Table'[Completed Date] >= DATE ( 2021, 2, 1 )
            && 'Table'[Task] = 2
            && 'Table'[Who?] IN { "Amy", "Phil", "Phil", "Alex" }
    )
)
    / COUNTX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Completed Date] >= DATE ( 2021, 2, 1 )
                && 'Table'[Task] = 2
                && 'Table'[Who?]
                    IN { "Amy", "Phil", "Phil", "Alex" }
                        && 'Table'[Completed Date].[Month] IN DISTINCT ( 'Table'[Completed Date].[Month] )
        ),
        [Condition]
    )

chart.png

 

Best Regards,
Community Support Team _ Yingjie Li
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

10 REPLIES 10
v-yingjl
Community Support
Community Support

Hi @MetricsBI  ,

You can create this measure:

percent = 
COUNT ( 'Table'[Condition] )
    / CALCULATE (
        COUNT ( 'Table'[Condition] ),
        ALLEXCEPT ( 'Table', 'Table'[Completed Date].[Month] )
    )

line chart.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Yes. I think this will work! but now I'm running into a different problem with the same data since there are more rows and the formula you provided is taking all of the rows and some need to be excluded. For example.. in the data i want to include all rows that are on or after 2-1-21, Task = 2, and only want to inlcude Amy Phil, and Alex. I'm sorry for making this more complicated but wanted to try and make it as simple as possible with my first question.

 

MetricsBI_0-1623962713667.png

 

 

Hi @MetricsBI ,

You can adjust the measure like this:

percent =
CALCULATE (
    COUNT ( 'Table'[Condition] ),
    FILTER (
        'Table',
        'Table'[Completed Date] >= DATE ( 2021, 2, 1 )
            && 'Table'[Task] = 2
            && 'Table'[Who?] IN { "Amy", "Phil", "Phil", "Alex" }
    )
)
    / COUNTX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Completed Date] >= DATE ( 2021, 2, 1 )
                && 'Table'[Task] = 2
                && 'Table'[Who?]
                    IN { "Amy", "Phil", "Phil", "Alex" }
                        && 'Table'[Completed Date].[Month] IN DISTINCT ( 'Table'[Completed Date].[Month] )
        ),
        [Condition]
    )

chart.png

 

Best Regards,
Community Support Team _ Yingjie Li
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

lbendlin
Super User III
Super User III

This looks a little ugly but it does what you want.  Will look better if all conditions are present in all months

 

lbendlin_0-1623711757610.png

 

Design alert:  You should only use line charts for data that has a clear continuity. In your case completions in one month are not dependent on completions in another month, so you should rather use a 100% stacked column chart. Incidentally you can do that without any measures.

 

lbendlin_1-1623713514878.png

 

 

lbendlin
Super User III
Super User III

That request is different from your original request. Are you planning to show the Condition field in the legend? In simplest terms, are you planning to show a stacked line chart or a 100% stacked line chart?

100% stacked line chart

Ashish_Mathur
Super User III
Super User III

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello - please see below. I want this to be a line chart similar to my orgianl table but I need each month to make up 100%. For example in January everything was completed on time so it should be 100% then Feburary 5 of 6 were completed on time so it should say 83.33%

 

MetricsBI_0-1623676304931.png

 

 

Hi,

Create a Calendar Table and build a relationship from the Completed Date column of the base data table to the Date column of the Calendar Table.  In the Calendar Table, write calculated column formulas to extract Year, Month name and Month number.  Sort the Month name by the Month number.  To your visual, drag year and Month name from the Calendar Table.  Write these measures:

Total = countrows(data)

On time completion = calculate([total],Data[condition]="completed on time")

On time completion (%) = divide([On time completion],[Total])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User III
Super User III

Don't use percentage of Grand Total.  Use Percentage of Row Total.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors