Forum Discussion
Helper IIChanging totals on tables
Guys is there any easy way to make the column totals a sum rather than an average in the table below :
New Mesuare = SUMX( VALUES(family id[family id]), [price per item]))
excuse me forget to close the parentheses
11 Replies
Richard76Sorry didnt explain very well. The figure in the table is a new measure (price per item = sales value / qty) so I'm happy with that. But I just want the total along the bottom to be sum rather than an overall average if that makes sense...
PaulDBrownCommunity Champion
Try this. Create a new measure to calculate the sum of values you want to display. Then use the following measure in your table:
measure = IF(HASONEFILTER(family id [family id]), [price per item], [sum measure])
(you obviously need to use the column and measure references appropriate to your model).
See if that works,
regards,
Paul.
I tried with my dataset. Here is how it looks with all SUM -
I created a measure as follows -
SalesMeasure = IF(HASONEFILTER(Orders[Category]),AVERAGE(Orders[Sales]),
CALCULATE(SUM(Orders[Sales]),ALLEXCEPT(Orders,Orders[Region])))And used this measure in below matrix table -
Does this look similar to what you want?
Regards
- Richard76
I think the formulae you showed me is close but still can't get it to work. So the values in the above are averages but I want the total line for Jan17 to be £23.76 (£11.09+£12.67), Feb to be £24.2 and so on so I can plot the totals on a chart
- PaulDBrown
Helo,
Try this:
create a new measure = SUMX(VALUES(family id[family id]), [price per item]
Hello,
Try this:
New Mesuare = SUMX( VALUES(family id[family id]), [price per item]
greetings
Galo
New Mesuare = SUMX( VALUES(family id[family id]), [price per item]))
excuse me forget to close the parentheses
- Richard76
Thank you very much for your help that has solved it
