Data with different granularity - basic sample - need advice

[Lolo]

Hello,

I want to have a dashbaord to calculate a perf between the number of sales by user, compared to a sale's objective that is by user.


So I have a table that list all sales. A line by sale (and by user)
and a table by user with the target desired for each user.

I just want to compare the number of sales by user by the objective of the user, to have a % of perf.

I have made a basic sample file that seems to works correctly, and want to be sure if what I have done is the good way in term of model and in term of DAX measure, or if there are some other way to do that.


Indeed, each time I'm not sure how to manage cases where data are in different tables with not the same level of granularity, and when I need to use in measures, data that are not in the same tables.
I know that in case of model with detail and headers tablesfor example, 1 solution is to have a flattened model, with 1 central table (header is merged into the detailed table, but in my present case, I don"t see how I could do this, the objective cannot be merged into the detailed table...


Thank you for your precious help.

 

[Chihiro]

I'd do it like below.
1. Add measure for userCount

userCount:=COUNTROWS(T_DETAIL)

2. Use Divide function, which will handle div0 and other error types.
This is more accurate, as C has no Objectif defined. 0 divided by null should be null.

Perf:=Divide([userCount],SUM(T_OBJ[Objectif]),0)

You could combine it into one measure, but I prefer to have these types of measure done in stages to easily trace issues, when needed.

 

[Lolo]

Thank you.
wel noted about the 2 measures, arnd the usage of DIVIDE function in case of null values.

However, you use SUM, while I'm using SUMX, not usre in this case to understand the difference. I thought that it was obliged to use SUMX, little bit confusing for me

So I understand that globally this is the standard way to manage this : the user table is a dimension that can contains elements (user's objective), that can be used in a measure and be compared to fact tables (count of user's detail sales),


Thank you.

 

[Chihiro]

The difference between SUM & SUMX is that. SUM is an aggregator function, which adds up "ALL" the value in a column in "current" filter context.

Where as SUMX is an iterator function. It iterates through table row by row to complete calculation in "current" filter context.

Both can end in same result, however, SUMX accepts row context expression within it's argument.

Syntax: = SUMX(<Table>, <expression> )

Therefore, as an example, SUMX can be used to calculate sum of [Qty]*[Price] for each row. But SUM cannot.

In your case, since only PivotTable's filter context is required for calculation, both functions will return same result. In this instance, SUM is more efficient than SUMX.

EDIT: Have a read of link below for more detailed explanation of SUM, SUMX and CALCULATE.
https://powerpivotpro.com/2014/10/sum-sumx-or-calculatechoices-choices/

 

[Lolo]

Thank you