Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi PBI Community,
I have a data table that contains the following row items :
I want to populate the row items as below :
Net Sales is a calculated row which is calculated as Sales - Adjustments,
Gross Profit is calculated as Net Sales - Cost of Goods
and Operating Income is calculated as Gross Profit - Admin Expenses.
Any help on this is appreciated.
@amitchandak @Ashish_Mathur @Ritaf1983 @parry2k @MFelix
Solved! Go to Solution.
Hi,@DeepakJha23 .
I am glad to help you.
According to your description, you want to realise the Row level calculation.
Here is my test.
You can create the following DAX function to do what you want.
Net Sales = 'Table'[Sales]-'Table'[Adjustments]
Gross Profit = 'Table'[Net Sales]-'Table'[Cost of Goods]
Operating Income = 'Table'[Gross Profit]-'Table'[Admin Expenses]
You can then use these calculated columns after that.
However, if you want to implement calculations between rows for each object property, you can use transposition, where you need to redefine new columns based on the previous column names to display the column names of the table before transposition. But this will invalidate the previously created calculated columns.
You could try to use the calculate function to calculate the data by limiting the scope of the calculation (but here, after transposition, the data from the previous different columns are now grouped into one column based on the object).
Here is the data after transposition.
In my opinion ,this is not good for the user to manage the data, so I recommend you to use calculated columns for your data calculations.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,@DeepakJha23 .
I am glad to help you.
According to your description, you want to realise the Row level calculation.
Here is my test.
You can create the following DAX function to do what you want.
Net Sales = 'Table'[Sales]-'Table'[Adjustments]
Gross Profit = 'Table'[Net Sales]-'Table'[Cost of Goods]
Operating Income = 'Table'[Gross Profit]-'Table'[Admin Expenses]
You can then use these calculated columns after that.
However, if you want to implement calculations between rows for each object property, you can use transposition, where you need to redefine new columns based on the previous column names to display the column names of the table before transposition. But this will invalidate the previously created calculated columns.
You could try to use the calculate function to calculate the data by limiting the scope of the calculation (but here, after transposition, the data from the previous different columns are now grouped into one column based on the object).
Here is the data after transposition.
In my opinion ,this is not good for the user to manage the data, so I recommend you to use calculated columns for your data calculations.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |