total per row is inaccurate

[sclencioco]

Hello,

Need help on this, computation of %age per month is accurate. Formula I used is this:

%age = (actual / expected) * 100

expected answer should be: (33247 / 43099) * 100 = 77.14% ; BUT the screenshot shows 1008.24%

Please help. Thank you.

Saldie

[v-yulgu-msft]

Hi @sclencioco,

According to this thread, I have two questions:

1. How did you create the table as the image show, in PowerBI or Excel?
2. How did you calculate all the values in Total row, especially the total of column "no of stations" and "expected"? It seems that value 131 and 43099 are not the sum of corresponding columns.

Obviously, the incorrect result 1008.24% is the total of all the percentage values above. In order to return expect value: (actual / expected), please use below formula to create a measure:

%age measure =
CALCULATE (
    (
        LASTNONBLANK ( 'total per row'[atual], 1 )
            / LASTNONBLANK ( 'total per row'[expected], 1 )
    ),
    ALLEXCEPT ( 'total per row', 'total per row'[MonthName] )
)

Here is the output in my test. I get the result 91.60% rather than 77.14%, because I do not know how to calculate the value 43099.

If you have any question, please feel free to ask.

Best regards,
Yuliana Gu

[sclencioco]

Hi @v-yulgu-msft

#1, its PowerBI

#2, here's the measurement of no of stations and expected

no of stations = CALCULATE(DISTINCTCOUNT('2015_POSdata'[POS-Active Stations]),ALLEXCEPT('2015_POSdata','2015_POSdata'[MonthName]))

expected = '2015_POSdata'[no of stations] * [monthend day]

As I try to replicate, It did not work due to error, "Column 'actual' in table can not be found or may not be used in this expression.

Thank you.

Saldie

[v-yulgu-msft]

Hi @sclencioco,

Why did Column 'actual' can not be found? Check the column name in your expression. Also, is it a column or measure in your table?

Thanks,
Yuliana Gu

[sclencioco]

Hi Yuliana,

Its a measurement.

[v-yulgu-msft]

Hi @sclencioco,

Two ways:

1. Now that "actual" and "expected" are all measures, you can directly using [actual] /[excepted] to calculate the percentage value. In my test, the total value is correct.

2. Switch the measure to column, 

   New column actual=SUM('total per row'[actual])

   New column excepted=SUM('total per row'[excepted])

   then, use the measure in my original post.

If you have any other question, please feel free to ask.

Thanks,
Yuliana Gu