Help to create Rate with shown date

[saitks99]

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 !!

[saitks99]

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)

[v-rzhou-msft · Accepted solution]

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. 

[sevenhills]

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

[Megha166]

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?

[sevenhills]

Provide the DAX formula used ...