Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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]
)
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.
Hi @MetricsBI ,
You can create this measure:
percent =
COUNT ( 'Table'[Condition] )
/ CALCULATE (
COUNT ( 'Table'[Condition] ),
ALLEXCEPT ( 'Table', 'Table'[Completed Date].[Month] )
)
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.
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]
)
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.
This looks a little ugly but it does what you want. Will look better if all conditions are present in all months
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.
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
Hi,
Share some data and show the expected result.
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%
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.
Don't use percentage of Grand Total. Use Percentage of Row Total.
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |