Forum Discussion
Dynamically Calculate Sums Per Category in Measure
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?
Community ChampionThat'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.