Add the total of table to a value in the same table

[Johannesvd]

Hi all,

So i have the following challenge, i want to add the total of a table (column) to a value in that same table (without creating a dependancy error) sounds complicated but here's the explanation with simplified version of the data:

original:

TABLE	Original_values
BALANCE_CAR
CAR1_2016	100
CAR2_2016	100
CAR3_2016	100

So i want to add the total to the Balance_Car: 300

I cant create calculated columns so my idea is to create a temp table and than add the two values together in an new measure:
Result:

TABLE	Original_values	New temp table(sum original values)	New measure total original+temp
BALANCE_CAR		300	300
CAR1_2016	100		100
CAR2_2016	100		100
CAR3_2016	100		100

Any one got a idea?

[v-yulgu-msft]

Hi @Johannesvd,

In the original table you provided, is "BALANCE_CAR" a column header or column value? When doing the sum up calculation, how to group data records (values)? Please provide more detailed sample data to help us better understand your scenario.

Based on my assumption, your table is like:

Open Query Editor mode, add an index column first. Then, return back to report view mode, create some calculated columns referring to below formulas:

Column1 = LEFT(Sheet2[TABLE],3)

Column2 =
IF (
    RIGHT ( Sheet2[TABLE], 3 )
        = LOOKUPVALUE ( Sheet2[Column1], Sheet2[Index], Sheet2[Index] + 1 ),
    RIGHT ( Sheet2[TABLE], 3 ),
    Sheet2[Column1]
)

New temp table(sum original values) =
IF (
    Sheet2[Values] = BLANK (),
    CALCULATE ( SUM ( Sheet2[Values] ), ALLEXCEPT ( Sheet2, Sheet2[Column2] ) ),
    Sheet2[Values]
)

Regards,
Yuliana Gu

[Johannesvd]

Hi @v-yulgu-msft, @MFelix,

Thank you so much for trying to helpme. @v-yulgu-msft the info you provided is correct Balance_car is column value. The best way to compare it is I think with a balance and profit and loss is you want to present only the balance ledger account in a matrix or a table the balance wont be 0 because you are missing the profit and loss ledger accounts. In that situation you also want the total of the profit and loss parked on a balance general ledger account (something like "Result current accounting year").

So i want the table @v-yulgu-msft provided sliced by a matrix or a table with visual level filters excluding ether the BALANCE_CAR or the CAR1, CAR2 values. I cant create a calculated column but i can make the non balance values more identifiable by adding some recognition points so they are better to filter. 

I wass thinking about summarize or addcolumns to create a temp table but i dont know how to add the result of a measure to a specific value

[v-yulgu-msft]

Hi @Johannesvd,

---

Thank you so much for trying to helpme. @v-yulgu-msft the info you provided is correct Balance_car is column value. The best way to compare it is I think with a balance and profit and loss is you want to present only the balance ledger account in a matrix or a table the balance wont be 0 because you are missing the profit and loss ledger accounts. In that situation you also want the total of the profit and loss parked on a balance general ledger account (something like "Result current accounting year").

So i want the table @v-yulgu-msft provided sliced by a matrix or a table with visual level filters excluding ether the BALANCE_CAR or the CAR1, CAR2 values. I cant create a calculated column but i can make the non balance values more identifiable by adding some recognition points so they are better to filter. 

---

I was confused about above description. It looks like the current requirement is different from the one in original post. What is the profit and loss? And you mentioned about excluding some records by visual level filters, so, based on the sample data in my above reply, what is your desired output? 

By the way, if you have any new question, please post it as a new thread so that more community members can view it. 

Best regards,
Yuliana Gu

[Johannesvd]

HI @v-yulgu-msft,

My apologies if i caused confusion i will try to clarify it with two screenshots:
First the old situation and following the new:
OLDNEW

I cant add calculated columns but can only work with measures.

[v-yulgu-msft · Accepted solution]

Hi @Johannesvd,

For calculated column

New temp table(sum original values) =
IF (
    Sheet2[Values] = BLANK (),
    0 - CALCULATE ( SUM ( Sheet2[Values] ), ALLEXCEPT ( Sheet2, Sheet2[Column2] ) ),
    Sheet2[Values]
)

For measure

measure New temp table(sum original values) = CALCULATE(SUM(Sheet2[Values]),ALLEXCEPT(Sheet2,Sheet2[Column2]))

Measure New temp = IF(max(Sheet2[Values])=BLANK(),0-[measure New temp table(sum original values)],MAX(Sheet2[Values]))

And you need to add [Column2] into table visual.

Regards,

Yuliana Gu

[Johannesvd]

Thanks, i can work with this!

[Johannesvd]

Thats a good question, this is only a part of the data. The reason why i want to add it this way is because we get a kickbackfee for the total (the last column) and i want to do some aditional calculations on that data. The visual gives me the number but i cant work with it in other measures.

[MFelix]

Hi @Johannesvd,

as a good practice you should always use measures if you can and not add columns to your model, columns add size to your files and are always recalculated upon model refresh and measure are calculated at visual level so not adding to your model, also measures allow more flexebility since they are calculated based on context soo if you add on you model a column with the total value if you want to have a filter on the data you will always get the total value and not a filter value.

You can in your measures something like this:

% of sales =
DIVIDE (
    SUM ( Table[VALUES] ),
    CALCULATE ( SUM ( Table[VALUES] ), ALLSELECTED ( Table[BALANCECAR] ) )
)

In this example I'm getting the total Sales for each CAR and dividing them by the total sales the last part is the one you want to have calculated in your table so you can do something similar.

Without having further knowledge of what type of information you want can't give you better practices.

If you still want to have it on your column I can also setup an example in Query Mode so that in your table the values appear.

Regards,

MFelix

[MFelix]

Hi @Johannesvd,

Just to give you a better explanation and help why do you want to had a total value column in your table?

In any visual you can have the totals  added without creating a new row/column on your data table.

Regards,

MFelix