Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I have a dataset displaying the P&L for different periods/types.
The columns show the values for: Previous Year, Actuals, Forecast, Dec Forecast, etc.
Rows show the accounts: Sales, Margin, etc.
I am trying to display this information on a Matrix Visualization.
What we would like to do is for example to use as rows Sales, Margin and "create" a third row with a measure of the margin percentage for each one of the scenarios, which are displayed in columns. The idea is that this calculation would vary according to any filters that are applied.
Example:
Accounts | Previous Year | Actuals | Forecast | Dec Forecast
Sales 100 100 100 100
GM 20 30 40 55
% GM 20% 30% 40% 55%
Does anyone have an idea how to do it or if it is possible to be done?
Thank you,
Solved! Go to Solution.
Hi @BrunoBarbosa,
A normal solution is that you may need to create different measures for Previous Year Sales, Previous Year GM, Previous Year %GM, Actuals Sales, Actuals GM, etc.
And you also need to create a table with a column called "Accounts Type" contains values "Sales", "GM", "%GM", and create another table with a column called "periods/types" contains values Previous Year, Actuals, Forecast, Dec Forecast, etc.
Then, you can make use of IF and SWITCH Function (DAX) to create a new measure to choose what value should be return depend on current values of "periods/types" and "Accounts Type", and show it on the Value field of the Matrix visual, with "periods/types" column set on Row field, and "Accounts Type" column set on Column field.
It's a little complex, but it could be done.
Regards
@BrunoBarbosa , How are you? Como é que vai? Heheh! I am from Brazil. Don't know if you are, too.
Since last year I was trying to accomplish the same thing (I think), but I could not find this post before.
Today I got back to this issue (challenge) and saw this post with a way to do so. I have simulated a very small dataset and then tried to apply the suggestions, but in my case I did not create other tables. This is the reason I am posting my files here today!
I simply added new lines to my dataset (just to have this line) in order to be possible to use DAX. I advance that in case you cannot add physical lines in the original dataset, I think you could then create another dataset just with the lines (eg: Revenue / Billings, Compensation / Revenue as I created) and date columns and then append to original dataset using Power Query, for example.
One Drive Link: https://1drv.ms/f/s!Aq2_y0xKTrAvgqQaf7PIGkZVQYLFwQ
If you have any problem with download, please let me know.
I hope this can help others, too.
Thanks and cheers,
Jimmy
Hi @BrunoBarbosa,
A normal solution is that you may need to create different measures for Previous Year Sales, Previous Year GM, Previous Year %GM, Actuals Sales, Actuals GM, etc.
And you also need to create a table with a column called "Accounts Type" contains values "Sales", "GM", "%GM", and create another table with a column called "periods/types" contains values Previous Year, Actuals, Forecast, Dec Forecast, etc.
Then, you can make use of IF and SWITCH Function (DAX) to create a new measure to choose what value should be return depend on current values of "periods/types" and "Accounts Type", and show it on the Value field of the Matrix visual, with "periods/types" column set on Row field, and "Accounts Type" column set on Column field.
It's a little complex, but it could be done.
Regards
Here is one interesting link and can help you. It is not direct solution to your question but get you started
https://community.powerbi.com/t5/Desktop/Create-abberanding-total/td-p/115909
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |