Forum Discussion
Help to create Rate with shown date
I want to restrict all up October rate only for those days where renewal count is available (for october is should show 90.8% rather than 28% currently all up october rate is showing).
Help appreciated !!
Hi saitks99
I think you want to change the total from 24.2% to 90.7% and keep other values unchanged.
sevenhills ‘s way will show NA if Rate = 0.0%. You can try my way.
My Measure:
Rate = VAR _T = CALCULATETABLE ( 'Query1', FILTER ( 'Query1', 'Query1'[RenewalCount] <> 0 ) ) VAR _Result1 = DIVIDE ( SUM ( Query1[RenewalCount] ), SUM ( Query1[EligibleCount] ) ) VAR _Result2 = DIVIDE ( SUMX ( _T, [RenewalCount] ), SUMX ( _T, [EligibleCount] ) ) RETURN IF ( HASONEVALUE ( Query1[Date] ), _Result1, _Result2 )Result is as below.
You can download the pbix file from this link: Help to create Rate with shown date
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Provide the DAX formula used ...
Can you please elaborate a bit more. Would be helpful if you can mention below points:
1. Current Chart
2. Expected Chart
3. Rate: This measure already exists or is this a measure created by you? If you have created this measure, then please share the DAX Query.
4. Also, the above chart is the expected one or existing one?
Current Rate Calculation for Entire october is giving me : 24.2% (2.59M/10.710M)
Instead, I want the measure to only do
Measure = (SUM of all renewal users for october)/ (SUM of all eligible users for october when renewal count is not 0)
= 2.597M/2.863M
=90.7%(This should be expected output from the measure)
Rate = CALCULATE( SUM(Query1[RenewalCount])/Sum(Query1[EligibleCount]) , FILTER(ALL(Query1[RenewalCount]), Query1[RenewalCount] > 0))ALL or ALLSELECTED can be used based on your requirement
Hi saitks99
I think you want to change the total from 24.2% to 90.7% and keep other values unchanged.
sevenhills ‘s way will show NA if Rate = 0.0%. You can try my way.
My Measure:
Rate = VAR _T = CALCULATETABLE ( 'Query1', FILTER ( 'Query1', 'Query1'[RenewalCount] <> 0 ) ) VAR _Result1 = DIVIDE ( SUM ( Query1[RenewalCount] ), SUM ( Query1[EligibleCount] ) ) VAR _Result2 = DIVIDE ( SUMX ( _T, [RenewalCount] ), SUMX ( _T, [EligibleCount] ) ) RETURN IF ( HASONEVALUE ( Query1[Date] ), _Result1, _Result2 )Result is as below.
You can download the pbix file from this link: Help to create Rate with shown date
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
