The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
Solved! Go to Solution.
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.
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.
Thanks for looking into it. The problem with the data set is that Suf could go to 999. At the end, I am more interested in total of Suf 0 and then the total of all Suf >0.
Also I need the data in a new table with Total Order, Total Suf 0 and Toal for Suf>0
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
246 | |
119 | |
114 | |
87 | |
70 |