cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Changing totals on tables

Guys is there any easy way to make the column totals a sum rather than an average in the table below :

 

Table.PNG

1 ACCEPTED SOLUTION

Accepted Solutions

New Mesuare = SUMX( VALUES(family id[family id]), [price per item]))

 

excuse me forget to close the parentheses

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

Hi @Richard76,

 

Shouldn't this do the trick?

 

total.PNG

Sorry 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...

@Richard76

 

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. 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi thanks for this but still no joy . 

I'm trying to get my head around DAX so what exactly is this calc doing ??

 

 

Skilled Sharer
Skilled Sharer

@Richard76

I tried with my dataset. Here is how it looks with all SUM -

 

 

ALLSUM.JPG

 

 

 

 

 

 

 

 

 

 

 

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 -

SUMANDAVERAGAE.JPG

 

 

 

 

 

 

 

 

 

 

 

Does this look similar to what you want?

 

Regards

Capture.PNG

 

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

@Richard76

 

Can you post the measure you are using?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Regular Visitor

Helo,

Try this:

create a new measure  = SUMX(VALUES(family id[family id]), [price per item]

Regular Visitor

Hello,

Try this:

New Mesuare = SUMX( VALUES(family id[family id]), [price per item]

greetings

Galo

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors