Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I am building financial report and I need to calculate Profit minus Operating Revenues row. Lets say, my table looks following:
Level 0 | Level 1 | Value |
Revenues | Sales | 100,00 |
Operating expenses | Outsourcing | 30,00 |
Operating expenses | Salaries | 50,00 |
Operating expenses | Other | 10,00 |
My question is, can I put in the Matrix table row called Revenues minus Operating expenses, which will calculate the result?
In excel and pivot tables, I am able to do that with option "Calculated item", and I would like to do the same in Power BI, as below:
Row Labels | Sum of Value |
1. Revenues | |
Sales | 100 |
2. Operating expenses | |
Other | 10 |
Outsourcing | 30 |
Salaries | 50 |
3. Revenues - Operating expenses | 10 |
All I can add is new column and I do not see option like "add calculated row". If you need more details please tell.
Best wishes,
Rafał
Solved! Go to Solution.
Hi, @rakunn
Please follow the screenshots to work around your issue.
Reference to DAX formulas:
Table1 = SUMMARIZE ( 'financial report', 'financial report'[Level0], 'financial report'[Level1], "SumOfValue", SUM ( 'financial report'[Value] ) ) Table4 = SUMMARIZE ( 'financial report', 'financial report'[Level0], "RowLebels", "Revenues - Operating expenses", "Level1", BLANK (), "SumOfValue", LASTNONBLANK ( Table1[SumOfValue], SUM ( 'financial report'[Value] ) ) - SUM ( 'financial report'[Value] ) ) Table 5 = SUMMARIZE ( SELECTCOLUMNS ( Table4, "RowLebels", Table4[RowLebels], "Level1", BLANK (), "SumOfValue", Table4[SumOfValue] ), [RowLebels], "Level1", BLANK (), "SumOfValue", SUM ( Table4[SumOfValue] ) ) Table6 = UNION(Table1,'Table 5')
If you have any question, please feel free to ask.
Best regards,
Yuliana Gu
Hello Everyone i have similar kind of issue, i need to calulate the percentage of the indiidual values with the total for each column for different month as shown in the figure.
Hi, @rakunn
Please follow the screenshots to work around your issue.
Reference to DAX formulas:
Table1 = SUMMARIZE ( 'financial report', 'financial report'[Level0], 'financial report'[Level1], "SumOfValue", SUM ( 'financial report'[Value] ) ) Table4 = SUMMARIZE ( 'financial report', 'financial report'[Level0], "RowLebels", "Revenues - Operating expenses", "Level1", BLANK (), "SumOfValue", LASTNONBLANK ( Table1[SumOfValue], SUM ( 'financial report'[Value] ) ) - SUM ( 'financial report'[Value] ) ) Table 5 = SUMMARIZE ( SELECTCOLUMNS ( Table4, "RowLebels", Table4[RowLebels], "Level1", BLANK (), "SumOfValue", Table4[SumOfValue] ), [RowLebels], "Level1", BLANK (), "SumOfValue", SUM ( Table4[SumOfValue] ) ) Table6 = UNION(Table1,'Table 5')
If you have any question, please feel free to ask.
Best regards,
Yuliana Gu
Hello Yuliana,
Many thanks for your time and effort put in your post.
DAX is truly impressive, but may be a little bit of intimidating for less experienced users (like me). Have you heard about some good online courses about this language? Or powerbi.com resources will suffice?
Hope to hear from you soon,
Rafał
Hi @rakunn
Microsoft provides many documents written about the snytax and usage of DAX function:
Data Analysis Expressions (DAX) Reference
Thanks,
Yuliana Gu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |