Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Jorn_Mazet
Regular Visitor

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:

Jorn_Mazet_0-1712137749361.png

The measure used in the visual is "Amount per level" and each line in the measure refers to another predefined measure.

Jorn_Mazet_1-1712137821839.png

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

Jorn_Mazet_2-1712138092088.png

 

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:

Jorn_Mazet_3-1712138298216.png

 

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

1 ACCEPTED SOLUTION

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

 

 

 

 

 

View solution in original post

8 REPLIES 8
Joe_Barry
Resolver V
Resolver V

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

 

 

 

 

 

Hey Joe,
I would have never guessed that this relationship would be the issue!!
Thanks!!
PS: the lower hierarchy issue was just a simple sum to be added to the master measure 😉

AmiraBedh
Resident Rockstar
Resident Rockstar

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



AmiraBedh_0-1712142277790.png

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

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...

AmiraBedh
Resident Rockstar
Resident Rockstar

I requested access to your file.


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Hi Amira,
Just gave access!
Thanks to look at the issue!!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.