Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello PBI Community,
I was wondering whether anyone could help me out on a specific problem I am having. I am trying to visualize a profit and loss statement in a matrix visualization similar to how I do with a PivotTable from Power Pivot in Excel. Despite the calculated measures in my pivot in excel (ie Gross Profit (GM1) ) are showing correctly in Power Pivot, they do not show within a Power BI matrix visual. Currently, I am using the following code to get my Pivot to display the way it does:
IS Subtotal Act =
IF (
COUNTROWS ( VALUES ( DIM_Headers[Header] ) ) = 1,
SWITCH (
VALUES ( DIM_Headers[Header Order] ),
1, [Rev Act],
2, [CoS Act],
3, [GM1 Act],
4, [SCO Var Act],
5, [SCO Fixed Act],
6, [OOIE Act],
7, [ADM Fix Act],
8, [OVC Act],
BLANK ()
),
100
)
I then apply this measure in the values field list and apply my headers, subheaders and account names on the rows to try and get my report to display correctly.
However, my end result looks something a little bit more like this:
I have also tried to remove the potential blanks in the underlying tables for my GM1 measure, by applying ALL to the measure as follows:
GM1 Act =
CALCULATE ( [Rev Act] - [CoS Act]),
FILTER (
ALL ( DIM_SubHeaders, DIM_SubHeaders[SubheaderName] ) )
This does not work either and returns an error.
What I am trying to achieve here is have my table display in the order of the measures by switching the values from the header table with the header order column below:
However, when I do so, the GM1 (Gross margin o.PPC/cost of sales) measure [GM1 Act] is simply blank and does not contain a value. However, when I select drill down, the correct value is calculated GM1:= [Rev Act] - [CoS Act] (Revenue - Cost of Sales), which is 14,000,000 (approx).
This is a really important project to me and it would be a shame to throw away what I have done. I am not sure why the visual displays correctly in a Pivot in Excel, but not in Power BI. This table is related to the DIM_Accounts table which contains accounts relating to their specific header and subheader, and then down to the General Ledger table as pictured below.
Do you have any idea why this would be the case?
Please help me out if you have time.
Hi
You might get some inspiration to solve the issue by looking at this method
http://www.thebiccountant.com/2016/07/21/easy-profit-loss-account-scheme-reports-power-bi-power-pivo...
/Erik
Thanks for referencing my article @donsvensen, but this would mean a complete re-work of this model.
@toddpbi: What happens if you eliminate the field "Subheader" from your report?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |