calculates column shows "infinite"

[juandroid]

Hi Guys

I have the next data:

With that data I build the next tables:

I need to create a new column that shows the % of each row over the Total Activos. First a create a new measure like this:

Diferencia porcentual entre SaldoReal y 1 =
VAR __BASELINE_VALUE =CALCULATE(
SUM('public v_cuentascontablesmes (2)'[SaldoReal]);
'public v_cuentascontablesmes (2)'[cuecod] IN { "1" })
VAR __MEASURE_VALUE = SUM('public v_cuentascontablesmes (2)'[SaldoReal])
return DIVIDE(__MEASURE_VALUE; __BASELINE_VALUE)

When I add the measure in one of the table reports, the result was:

As you can see, it shows the right % but only for the total and i need it in every row so I create a new column like this:

Columna = 'public v_cuentascontablesmes (2)'[SaldoReal]/CALCULATE(
SUM('public v_cuentascontablesmes (2)'[SaldoReal]);
'public v_cuentascontablesmes (2)'[cuecod] IN { "1" }
)

But when I add the column to the table report it shows like this:

Infinito means Infinite

I don't know what's wrong with the expression, please help!!

Thanks in advance

Juan D.

[v-jiascu-msft]

@juandroid,

Hi Juan D.

It's something about context. Try to add a FILTER function. 

Diferencia porcentual entre SaldoReal y 1 =
VAR __BASELINE_VALUE =
    CALCULATE (
        SUM ( 'public v_cuentascontablesmes (2)'[SaldoReal] );
        FILTER (
            ALL ( 'public v_cuentascontablesmes (2)' );
            'public v_cuentascontablesmes (2)'[cuecod] IN { "1" }
        )
    )
VAR __MEASURE_VALUE =
    SUM ( 'public v_cuentascontablesmes (2)'[SaldoReal] )
RETURN
    DIVIDE ( __MEASURE_VALUE; __BASELINE_VALUE )

Best Regards!

Dale

[juandroid]

@v-jiascu-msft

I try what you said but it didn't work. The result was:

As you can see, now it shows values in each row but they are wrong, the total of that % it should be 50.28, like appears in the pic of the first topic. 

[v-jiascu-msft]

Hi @juandroid,

Can I ask what the meaning of this formula is? In the total part, __MEASURE_VALUE should be bigger than __BASELINE_VALUE, which is only the sum of "saldoreal" when "cuecod" equals 1.

Best Regards!

Dale

[juandroid]

Hi @v-jiascu-msft

Each account that starts with 1 is an asset account for example:

Cuecod    acountname              SaldoReal    CtbempCod

1             Total Assets                  190                 1

11           Mother Assets              150                 1  

1105       Cash                              50                   1

1110       Banks                            50                   1

1111       International Banks       50                  1

12           Father assets                 40                  1 

1205       wherever name1           20

1210       wherever name2           20

the data table contains accounting info, so the column CUECOD is the account number and SALDOREAL the value. I'm trying to create a measure that shows the % of each row over the total asset (wich is the SALDOREAL for the CUECOD = "1"). In the first post, the second image shows all the tables that I create including a CARD called Total Activos (the green square) that is simple the sum of the values when CUECOD = 1, so i put SALDOREAL in Values and CUECOD is 1 in visual filters.

As you can see, the account "1" adds all the values of the assets. So what I want its to create a measure to show me the porcentage of each asset over the total of assets. That's why I'm doing this:

    CALCULATE (
        SUM ( 'public v_cuentascontablesmes (2)'[SaldoReal] );
                    'public v_cuentascontablesmes (2)'[cuecod] IN { "1" }
        )
    )
VAR __MEASURE_VALUE =
    SUM ( 'public v_cuentascontablesmes (2)'[SaldoReal] )
RETURN
    DIVIDE ( __MEASURE_VALUE; __BASELINE_VALUE )

 Because all the values are in the same column (SALDOREAL) I need to take the value of each asset and divided between the value of the aseet where the CUECOD equals "1". Also, as appers in the example table, theres a column called Ctbempcod wich is an id for each company we manage, so in the real DataTable you are going to find an account "1" for every company, obviously eah one with a different value in SALDOREAL, that's why I use a Sum expression.

I really hope this help you to understand a little bit more. 

Thanks a lot!

[v-jiascu-msft · Accepted solution]

Hi @juandroid,

A little weird. Because the __MEASURE_VALUE wasn't changed. Try this formula please.

Diferencia porcentual entre SaldoReal y 1 =
VAR __BASELINE_VALUE =
    CALCULATE (
        SUM ( 'public v_cuentascontablesmes (2)'[SaldoReal] );
        'public v_cuentascontablesmes (2)'[cuecod] IN { "1" };
        ALL ( 'public v_cuentascontablesmes (2)'[cuenom] )
    )
VAR __MEASURE_VALUE =
    SUM ( 'public v_cuentascontablesmes (2)'[SaldoReal] )
RETURN
    DIVIDE ( __MEASURE_VALUE; __BASELINE_VALUE; 0 )

If this can't work, please share your PBIX file. You can replace your data with dummy data.

Best Regards!

Dale

[juandroid]

Hi @v-jiascu-msft

It worked perfectly!!! But can you please tell me why? I don't understand why adding that ALL expression to the column cuenom worked.

Men you really save me!

Best regards,

[v-jiascu-msft]

Hi @juandroid,

You are welcome. Because I don't see your model, all I can guess is there are some other filters applied. 

Best Regards!

Dale

[Greg_Deckler]

Kind of hard to make out what is going on, but I bet dollars to donuts it is related to your "IN {"1"}" filter. Whatever that is doing, it is returning 0 or blank for the measure except in the Total line. This means that when you divide by it, you are dividing by zero and thus infinite?