Infinity error

[Anonymous]

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 (and cumulative) basis. Here is the measure:

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

However the last line is causing issues (it works exactly as I expect when I remove the final line). It is causing Infinities in my table visualisation so I assume it thinks the population is 0 for these months, but I'm unsure why. The table below is showing a line per region and Year-Month across the top. The error only seems to appear in months where there were no new registrations for the region.

The tables I have are:

Year-Month: one column showing all possible Year-Months (2019-12, 2020-01, 2020-02 etc)

This is where the measure is sat.

Registrations: one row per registration - relevant colums are UserID, Region, Year-Month.

One-Many relationship between Year-Month[Year-Month] and Registrations[Year-Month].

Regions: one row per region - relevant columns are Region, Population.

One-Many relationship between Regions[Region] and Registrations[Region].

Please can you help me identify where the measure is going wrong? Thank you.

[v-gizhi-msft · Accepted solution]

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

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[amitchandak]

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-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

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?

[rajulshah]

Hello @Anonymous,

Maybe the following link can help you:
https://community.powerbi.com/t5/Desktop/Filling-the-blanks-of-a-running-total-with-last-non-blank-value/td-p/169041

[Anonymous]

Hi @rajulshah thank you but my formula is working fine with just the numerator, it's when I try and divide this cumulative sum by a denominator that the Infinities/blank cells appear. The error only appears when there are no new registrations in a month.

Does anyone have any further ideas? amitchandak

[amitchandak]

Your are viewing data by Year and denomintor is coming another table. How it is becoming 0. Can you share some sample data.

[rajulshah]

Hello @Anonymous,

Please use DIVIDE for division in calculation. This will remove 'Infinity' label.