Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Infinity error

Hello,   I have some data about registrations to an online system. I want to create a measure showing the percentage of registrations out of the total population for a region, on a month-by-month (...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    According to your description, i create three tables to test:

    Please take following steps:

    1)Create a measure:

    Measure = DIVIDE(COUNTROWS(Registrations),SUM(Regions[Population]))

    Choose a matrix visual and it shows:

    2)Create another measure:

    Measure 2 =
VAR d =
    SELECTEDVALUE ( 'Year-Month'[Year-Month] )
RETURN
    IF (
        COUNTROWS ( 'Registrations' ) = 0,
        VAR t =
            FILTER (
                SUMMARIZE (
                    FILTER ( ALLSELECTED ( 'Year-Month' ), 'Year-Month'[Year-Month] < d ),
                    'Year-Month'[Year-Month].[Year],
                    'Year-Month'[Year-Month].[MonthNo],
                    "Number", CALCULATE ( COUNTROWS ( 'Registrations' ) )
                ),
                [Number] > 0
            )
        RETURN
            CALCULATE (
                [Measure],
                FILTER (
                    ALLSELECTED ( 'Registrations' ),
                    'Registrations'[Region] IN FILTERS ( 'Registrations'[Region] )
                        && MONTH ( 'Registrations'[Year-Month] ) = MAXX ( t, [Year-Month].[MonthNo] )
                        && YEAR ( 'Registrations'[Year-Month] ) = MAXX ( t, [Year-Month].[Year] )
                )
            ),
        [Measure]
    )

    And it replace the original blank with previous month's data, the result shows:

    Here is my test pbix file:

    pbix 

     

    Best Regards,

    Giotto ZHi

amitchandak's avatar
amitchandak
Super User
6 years ago

Try like

Adoption Rate3 = 
 iferror(divide(calculate(
 counta('Registrations'[UserId]),
 filter(allselected('Year-Month'[Year-Month]),
 ISONORAFTER('Year-Month'[Year-Month],
 max('Year-Month'[Year-Month]),desc))),
 , sum(Regions[Population])),
 0)

 

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi rajulshah amitchandak thank you for your replies but this creates a different problem as now the Infinities are showing as blanks. As I mentioned this is a cumulative formula so it does not make sense to have blanks. If no registrations happened in a month, it needs to show the exact same value as the previous month. Please let me know how I can fix the error?