Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

lbendlin
Super User
Super User

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
Super User

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
Super User

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
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.