Forum Discussion
Financial Statement - Measure issue
Hi all,
I'm trying to create a measure to use in a matrix that uses other measures to show a financial structure:
The measure used in the visual is "Amount per level" and each line in the measure refers to another predefined measure.
All base measures are simple CALCULATE functions.
I'm using a structure built in Excel, where the TOTAL Turnover without ohter is defined within the structure. This structure is used as Row data in the visual
Trouble starts when I use a measure that simply adds up 2 other measures (my measure there is "TOTAL Turnover without other"); I want to show a line that adds up the "Recurring Turnover" (so level 2 - Recurring) and "Non-Recurring Turnover (so level 2 - Non-Recurring), while ignoring the "Other revenue" (TOTAL Turnover without other = [Non Recurring Turnover] + [Recurring Turnover]).
I don't understand why Power BI does not show the result of TOTAL Turnover without other, while every line is in fact a measure:
When I change the measure for the total without other to the value of '1', it shows.
I'm at a loss and would appreciate any and all tips!!
All content (pbix + sources in Excel) available here
Hi Jorn
It seems to be a relationship issue. When I remove the Relationship between FACT and DT_GL_Structure the Total Turnover without other works. This is how I have my P&L set up too. But there are issues with numbers for the lower hierachy.
Unfortunately I don't hav etime to look into it more, but hopefully this will help you find a way.
Thanks
Joe
8 Replies
I requested access to your file.
Hi Amira,
Just gave access!
Thanks to look at the issue!!
I found it because simply the GL_account is empty or NULL in the DT_GL_Reporting Structure table for Level1 =
TOTAL Turnover without other The GL_account is empty because line for the line "TOTAL Turnover without other" is suppose to be calculated by adding 2 other measures instead of summing GL accounts; that is the premise of the question...
Hi Jorn_Mazet
Maybe this articles will give you an overview Parent-child hierarchies – DAX Patterns then go to >
Chart of accounts hierarchy
Hope this helps!
Joe
Hey Joe,
Thanks for taking the time to look at my post.
The issue I'm having is not an issue of hierarchy for as far as I can see, it's an issue of a measure that doesn't show a result in my matrix (where standalone it does).
If you have insights in this specific issue, I'd love to hear from you!Hi Jorn
It seems to be a relationship issue. When I remove the Relationship between FACT and DT_GL_Structure the Total Turnover without other works. This is how I have my P&L set up too. But there are issues with numbers for the lower hierachy.
Unfortunately I don't hav etime to look into it more, but hopefully this will help you find a way.
Thanks
Joe
