Correct way to calculate totals per visual

[gvg]

Hi folks,

I been reading a number of threads how to correctly calculate totals in a visual. In all of the examples a measure very similar to this, described by Rob Collie, is used:

[Sales per Day FIXED] = 
IF(COUNTROWS(VALUES(Category[Name]))=1,
   [Sales per Day],
   SUMX(VALUES(Category[Name]), [Sales per Day])
)

I am confused with the SUMX function that incorporates VALUES function. VALUES returns distinct values in a column. But what if I need to calculate totals on all the values in a column, not just distinct values? For example, if my Category[Name] looks like this:

Date           Name     Sales per day

1/1/2001    Bikes       10

2/1/2001    Bikes       30

2/1/2001    Bikes       15

1/1/2002    Bikes       20

1/1/2002    Bikes       40

1/1/2002    Bikes       35

Apparently I can not use 

SUMX(VALUES(Category[Date]), [Sales per Day])

as there are rows with similar dates there. What would be the measure that calculates correct total in a visual regardless if my column has distinct values or not?

[Ashish_Mathur]

Hi,

What result are you expecting?

[MFelix]

Hi @gvg,

The SUMX is an aggregator formula that as you said evaluates your calculations row by row and return the sum of that in the total column, this is the principal behind all X's formulas in DAX (AVERAGEX, MINX, ...).

Looking at the data you are providing in your example you only need to do a simple SUM and then giving the correct context to your data in the visuals will return the correct result.

The context of the measures is very important to get the correct result, so be carefull when trying to apply in a standard way the calculations on DAX because you can get unnexpected results.

Regards,

MFelix

[gvg]

Hi @MFelix, @Ashish_Mathur

Yes, the example is simple, but Sales per day is actually a calculated measure and I can't get correct Total since I do not have any column in the visual that has distinct values.

[Ashish_Mathur]

Hi,

Just share the link from where i can download your PBI file and show the expected result there.

[MFelix]

Hi @gvg,

the example you give doesn't need to have the SUMX working you cand do a simple sum and then add the date and Name o a table visual or chart and the value will be correct since the date and name will give you context to your measure. Has I refered prevously the SUMX is used when you want to give additional context to your measures tha aren't evident in your visual.

For example if on the data set you have if you want to  count all the values that have daily sales above 20 you would then use the SUMX to make the context for each row to countthe above 20 rows would be something like this:

SUMX(Sales,IF(CALCULATE(Sales [Daily Sales])>=20 , 1 , 0))

This would give you 1 for each sales above 20.

Again context is very important when calculating a measure even a simple sum.

Regards,

MFelix

[gvg]

@MFelix,

well, as I said, Sales per day is a complicated measure, not a table column. And summing sales that are > 20 does not help as I need to sum everything that has been calculated in the visual column Sales per day.

[PaulDBrown]

@gvg

What I don't get is why you are getting more than one line per date in your example, given that there are no other filter contexts in the table...What exactly is the [sales per day] actually calculating? In other words, what is the [sales per day] measure taking into account (filter context) to generate more than one line per date?

[gvg]

Hi @PaulDBrown,

Please, do not look for any logic here. I made a simplified example in my original post. Maybe column Sales per day has a misleading title. Let's call it Margin to make sure this is a calculated column.

My question really is how can I calculate correct visual totals given that I have not a single column in the visual with unique values.

[PaulDBrown]

@gvg

Hello. While in the spirit of being constructive, try:

Totals = SUMX(SUMMARIZE('table', 'table'[date]), [Sales per Day])