Change the way total of column is displayed

[lgroger]

I'm getting stuck on something that seems like it shoud be so simple.  I have a dataset that has a row for each sale of a particular item.  That row also has an inventory level and unit cost which is the same for every record of the same item.  I have a column that displays the remaining inventory cost (inventory * unitcost) in a table that is displaying sales data per item.  I have the inventory cost set as "don't summarize" b/c I don't want it added together for each time it's sold.  However, I'd like to show a total of all item's remaining inventory cost at the bottom of the table but since I have it set to "Don't Summarize", it's not showing anything.  I've tried to solve it by creating measures but that doesn't give me an option to select how to aggregate it in the total and it seems to be guessing how to summarize it in the "Total" row based on my functions used.  For example, if my measure is average(Table[inventory])*average(Table[unitcost]) then it's showing an average in the total column.  If I use max(Table[inventory])*max(Table[unitcost]), it's showing a max value in the total.  If I use sum(Table[inventorycost])/count(Table[order]), it seems to be applying that calculation to the entire table for the total column.  Is there a way to better control what is shown in the "Total" row of a column?

Example data

orderno	item	qtysold	sales	inventory	unitcost
101	ABC	5	49.00	100	5.00
101	XYZ	1	5.00	2	2.00
102	ABC	1	10.00	100	5.00
103	ABC	2	19.00	100	5.00
104	XYZ	2	9.00	2	2.00

[DouweMeer]

@AndreaPontiggia 

You could think of using average or min/ max. The inventory is the same anyway. Just throw the product in a table visual with an average of your inventory. If you want Sales, it will be sum of course.  

But personally, I would split the table in a sales table and an inventory table. Probably also add a product table. Will save you more time. Then you wouldn't have this problem. Besides, there should be no direct relationship like that between inventory and a purchase order. 

[lgroger]

Hi @DouweMeer ,  I've tried using average and min/max which works for the value per item, but when it's totaled at the bottom of the table,  it's not adding up all of the values like I want - it's doing an average, min or max (whatever function I used in the measure).

[DouweMeer]

@lgroger 

Uhm, remove the total and show the others in a card visual?

Totals in tables are not a summarization of the values above, but a calculation on the all values without the implicit filters. 

[lgroger]

@DouweMeer  We tried that too - you don't get an option of how to show a measure in a card and we have the same multi-counting issue in the card for the sum of the calculated column.

I guess I'm going to try to make a separate calculated table and then use a card visual for that.

[Mariusz · Accepted solution]

Hi @lgroger 

You can use the below 

M1 = max( Table1[inventory] ) * max( Table1[unitcost] )

M2 = 
SUMX(
    GROUPBY(
        Table1,
        Table1[orderno],
        Table1[inventory]
    ),
    [M1]
)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lgroger]

Thanks, @Mariusz.  This worked but it seems like this shouldn't have to be so complicated!  I submitted an idea if anyone is having similar issues and wants to vote for it!  Thanks!

https://community.powerbi.com/t5/Issues/control-how-table-total-row-is-calculated-per-column/idi-p/7...

[lgroger]

I posted my idea in the wrong forum apparently 😞  Here is a link to a similar idea that I've voted for.  If anyone else has this same need, please vote for it! 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/33553138-customize-totals

[DouweMeer]

@lgroger  You could try something like :

Inventory =

VAR a1 = distinct ( selectcolumns ( allexcept ( 'table' , 'table'[item] ) , "Inventory" , 'table'[inventory] )

RETURN

sumx ( a1 , [inventory] )