DAX Measure - Group Total in each customer number row

[SvenCleut]

Hi,

I have a seemingly simple question, however, I just can't seem to get it right. My question is the following: I have customers divided into CSAC3 groups. So a customer belongs to a specific CSAC3 group. I have also created a measure that calculates the turnover TY. However, I now want to have a measure that adds up the turnover of the CSAC03 group and displays it behind the relevant customer number with the associated CSAC03 group. What I keep getting now is that it filters this turnover by customer number each time anyway. Ultimately, I want to calculate further using this turnover per CSAC03 group. But I can't get this to work.
Below is an image of the table with columns Customer number, CSAC03 and Turnover MAP TY. This is on a customer number basis. Now I actually want another column next to it with Turnover MAP TY (CSAC03). So all the customer numbers with CSAC03 ‘4PLUS’ should include the total of this CSAC03 group.

Furthermore, the code of the measure, perhaps this also has something to do with it.

The tables are connected by means of customer keys/customer numbers.
If anyone can help, please do, thank you!

[mark_endicott · Accepted solution]

@SvenCleut - You need to show me the DAX you are using, whether you are using it in a Measure or in a column, sen some screenshots of your model, or file share your PBIX for me to resolve this. 

If you had copied the DAX structure exactly as I had given to you, I dont think this would be an issue. For reference, below is a screenshot of my DAX, and you can see it does not show cities that do not belong to countries:

[SvenCleut]

Hi @mark_endicott ,

You were right the code is correct. Only I had more columns in my table that caused it to filter on them. I had to put all these columns into the dax code using the REMOVEFILTERS() function. Thanks!

[mark_endicott]

@SvenCleut - If my understanding of your challenge is correct, you could actually achieve this by using the Matrix visual and drilling down to both levels of the rows (see below for an example): 

If you wish to implement an additional column in your table that shows the total for the CSAC03 group then you can use the DAX below:

CSAC03 Group Total = 

VAR _group = SELECTEDVALUE( Table[CSAC03] )

RETURN 
CALCULATE( [Omzet MAP TY], REMOVEFILTERS( Table[CustomerNumber] ), Table[CSAC03] = _group )

If either of these work for you, please accept as the solution to help others with the same challenge. 

[SvenCleut]

Hi @mark_endicott 
Thanks for your reply. This seems to work in some part. I do now get to see the total for each CSAC group. Only it now puts the total for each group at each customer number. So that is, each customer number now gets each csac group after its name, while this should not be the case. Each customer number can belong to only 1 csac group. See example below, which is now the case.

As you can see in the picture below, the only customer number with csac3 '4PLUS' is customer number 0000111415, which therefore has also a value at 'Omzet MAP TY' (there are more customer numbers with this csac03 group, but in this picture only 0000111415 is seen). The other customer numbers should not be shown with csac03 group '4PLUS'.

If you have a solution for this. I would be helped a lot!