Forum Discussion

saitks99's avatar
saitks99
Microsoft Employee
5 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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. 

saitks99's avatar
saitks99
Microsoft Employee
5 years ago

 

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)

  • sevenhills's avatar
    sevenhills
    Super User
    5 years ago

     

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.