Incorrect sum calculated

[poojik]

I have created a calculated field which is a combined filed of 2 already existing fields. The formula I have used is : 

Combined Units = if(ISBLANK(SUM(accounts[totalvotes])), SUM(accounts[units]), SUM(accounts[totalvotes]))

But the sum of combined units is not correct. Screen-shot attached. 

[MFelix · Accepted solution]

Hi @poojik,

I'm assuming that you are using a measure, in this case the calculations are made taking into account the context of the formula, so when you get to the total it's also calculated in that way and not the sum of the previous values, you need to place that value around aggregator.

Create a second measure with the following code to use on your table:

Measure 2 =
IF ( HASONEFILTER ( Table[Name] ); [Combined Units]; SUMX ( Table; Combined Units] ) )

For better performance change the Table in the SUMX by ALL( Colum 1 , column 2, ...) and choose all the columns need for grouping/sorting instead of placing the all table.

Ths formula calculate the measure on every single row, and on the total since it has no value it sums the previous rows.

Regards,

MFelix

[MFelix · Accepted solution]

Hi @poojik,

I'm assuming that you are using a measure, in this case the calculations are made taking into account the context of the formula, so when you get to the total it's also calculated in that way and not the sum of the previous values, you need to place that value around aggregator.

Create a second measure with the following code to use on your table:

Measure 2 =
IF ( HASONEFILTER ( Table[Name] ); [Combined Units]; SUMX ( Table; Combined Units] ) )

For better performance change the Table in the SUMX by ALL( Colum 1 , column 2, ...) and choose all the columns need for grouping/sorting instead of placing the all table.

Ths formula calculate the measure on every single row, and on the total since it has no value it sums the previous rows.

Regards,

MFelix

[poojik]

Thank you very much @MFelix this works. But logically I don't understand the logic behind HASONEFILTER(Table[Name]) . 

[MFelix]

Hi @poojik,

The HASONEFILTER check if in the current context (line) there is a name if it exists a name then it return the measure, if there is no name total or subtotal rows then it make the sum of the previous lines SUMX

Regards,

MFelix

[poojik]

Thanks @MFelix ! Makes sense. 

Once the sum is calculated I am trying to filter all those accounts with "Combined units" less than one third of "unit count". To do that I have created another calculated field : 

One third of Unit Count = IF((accounts[Combined Units] < (SUM(accounts[Unit Count])/3)), "Less than one thrid", "Greater than one third")

When I tried to count Accounts it only shows "Less than one third" but not showing "greater than one third". (screen-shot attached)

[MFelix · Accepted solution]

HI @poojik,

When you do a measure it's calculated based on context, so the formula that you have is calculating the division by the current row it will give you 1, you need to something like this:

One third of Unit Count =
IF (
    accounts[Combined Units]
        < CALCULATE ( ( SUM ( accounts[Unit Count] ) / 3 ), ALL ( Accounts[Unit Count] ) ),
    "Less than one thrid",
    "Greater than one third"
)

Regards,

MFelix