Forum Discussion
Dax code to summarise data based on two column inputs to determine the value
I have been looking every where for some assistance in summarising data with DAX code to get the following output. I have option 1 and 2 but preference is option 2.
| Original Data | ||
| Order | Suf | Amount |
| 500 | 0 | 200 |
| 500 | 1 | 150 |
| 500 | 1 | 200 |
| 500 | 2 | 300 |
| 501 | 0 | 400 |
| 502 | 0 | 300 |
| 502 | 0 | 200 |
| 502 | 1 | 400 |
| 502 | 1 | 300 |
| 502 | 2 | 100 |
| 503 | 3 | 100 |
Output
| Option 1 | |||
| Order | Amount | Suf | Amount |
| 500 | 850 | 0 | 200 |
| 500 | 1 | 350 | |
| 500 | 2 | 300 | |
| 501 | 400 | 0 | 400 |
| 502 | 1400 | 0 | 500 |
| 502 | 1 | 700 | |
| 502 | 2 | 100 | |
| 502 | 3 | 100 |
| Option 2 - Prefered | |||||||||
| Order | Total | Suf | Amount | Suf | Amount | Suf | Amount | Suf | Amount |
| 500 | 850 | 0 | 200 | 1 | 350 | 2 | 300 | ||
| 501 | 400 | 0 | 400 | ||||||
| 502 | 1400 | 0 | 500 | 1 | 700 | 2 | 100 | 3 | 100 |
You can create the calculated table like this.
Table 2 = SUMMARIZE ( 'Table', 'Table'[Order], "Total", SUM ( 'Table'[Amount] ), "Suf 0", CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Suf] = 0 ), "Suf > 0", CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Suf] > 0 ) )Or did you mean in a table visual which would be like this.
I have updated the attached .pbix with both.
3 Replies
Anonymous
There are a couple different ways you could organize it to get that view.
The second one is the simplest. I have attached my sample file for you to look at.
You can create the calculated table like this.
Table 2 = SUMMARIZE ( 'Table', 'Table'[Order], "Total", SUM ( 'Table'[Amount] ), "Suf 0", CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Suf] = 0 ), "Suf > 0", CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Suf] > 0 ) )Or did you mean in a table visual which would be like this.
I have updated the attached .pbix with both.