Forum Discussion
Dynamically Calculate Sums Per Category in Measure
Hello!
I have a calculation that needs to do this:
SUM(User Category A) * SUM(Values Category A) +
SUM(User Category B) * SUM(Values Category B) +
SUM(User Category C) * SUM(Values Category C) (ETC)
The solution would normally be a simple DAX function:
SUM(User Category) * SUM(Values Category)
However, the User Category is a calculation of total users, so summing the raw value always returns 1. I need these calculations split out individually.
Hard coding the categories returns the correct value, but the values will change over time, and slicers are needed looking at individual values, or different groupings. Is there any way for DAX to split these values dynamically? Something like...
FOR EACH CATEGORY
(
SUM(USER CATEGORY) * SUM(Values Category) + (previous iteration)
)
Data example:
| User Category | Data Category | |
| A | .50 | 100 |
| B | .40 | 200 |
| C | .10 | 300 |
Expected result:
.50 * 100 +
.40 * 200 +
.10 * 300
= 160
Getting Result:
1 * 600
= 600
I'm confused. Are you sure you are not overthinking this?
Did you have a look at the [Sales Amount] measure example and apply it to your use case?
If you did, can you show me what the results are and why it's not suitable?
7 Replies
- HotChilli
Community Champion
I'm confused. Are you sure you are not overthinking this?
Did you have a look at the [Sales Amount] measure example and apply it to your use case?
If you did, can you show me what the results are and why it's not suitable?
HotChilli
You were absolutely correct, I was over thinking it. Thanks for the help!
Solved in two measures based off your link:Percent Times Data = SUM(Test[Percent Users]) * SUM(Test[Data])Unique Category =VAR UniqueTable =ADDCOLUMNS(DISTINCT(Test[Category]), "Unique Categories", [Percent Times Data])VAR Result = SUMX(UniqueTable, [Unique Categories])Return Result
- HotChilli
iterator functions. SUMX unless I am misunderstanding the requirement.
HotChilli
Hello!
Could you please expand on that? Even if I iterate through each row, at the end of the day the full sum of the user category is 1. I don't know how to split the calculation into chunks based on the category without filtering each value manually:
Offhand, pardon syntax -
SUMX(FILTER(table, Category = "A"), User Category * Data Category) +
SUMX(FILTER(table, Category = "B"), User Category * Data Category) +
SUMX(FILTER(table, Category = "C"), User Category * Data Category)
I want it to be dynamic, so this can be done in one calculation that will be affected by slicers on the category column instead of hardcoded A, B, C.
- HotChilli
That's way too complicated. The power of an iterator is that a simple (!) piece of dax can do all that for you. You have the expression part correct but the first part (the table) is simpler
Have a look at this https://dax.guide/sumx/
In the examples near the top the 3rd one is similar to your case:
Sales[Sales Amount]HotChilli
My apologies, the table in my example is raw data, so I'm not concerned about generating the table. My goal is to not have to manually write every category in the DAX equation because I don't know what they will be.
It may be A, B, C for one set of data, B, C for another, etc. I want this controlled by slicers. If this isn't possible, I'll need to adjust DAX manually very time these values change.
So I'm looking for a way to dynamically multiply the user category by the data category and sum each individual row sum against the others.
For example, if using a different set of data the A and C columns no longer exists, but a D is added, I would manually have to go in and change this:
SUM(User Category A) * SUM(Values Category A) +
SUM(User Category B) * SUM(Values Category B) +
SUM(User Category C) * SUM(Values Category C)
to this:
SUM(User Category B) * SUM(Values Category B) +
SUM(User Category D) * SUM(Values Category D)
If the sum of the user category didn't always equal one this wouldn't be much of a problem, but because it's a percent of a total the rows need to be added against each other.
- HotChilli
I'm not at all happy about the resolution of this call. If I have understood the original question, these 2 measures are far too complicated.
If the data is the same as the original table posted (it doesn't help that the field names seem to have been changed further down the thread), the solution is one, simple SUMX measure (identical to the example I linked to - apart from table/field names obviously).
I don't like thinking that these 2 measures - will be used by anyone (including you) if there isn't a good solid understanding of context - which is what makes SUMX powerful.
--
If I have misunderstood anything, post some sample data that is different from the original data and show what the desired solution is.