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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Subtotals in table with switch(true()

Hey PBI peeps, 

 

I am trying to fill a small Income Statement and have trouble with subtotals. I am using this formula, to map specific measures to specific lines in the table (ubtotals are in bold):

 

Per laikotarpį priskaičiuoti atskaitymai =
VAR CurrentItem = SELECTEDVALUE('Income Statement Map'[Atsiskaitymai])
RETURN
SWITCH(TRUE(),
CurrentItem = "Kitos veiklos išlaidos", [Kitos veiklos išlaidos],
CurrentItem = "Už apskaitos paslaugas", [Už apskaitos paslaugas],
CurrentItem = "Už teisines paslaugas", [Už teisines paslaugas],
CurrentItem = "Už valdymą", [Už valdymą],
CurrentItem = "Sėkmės mokestis", [Sėkmės mokestis],
CurrentItem = "Už gautų paskolų palūkanas", [Už gautų paskolų palūkanas],
CurrentItem = "Už depozitoriumo paslaugas", [Depozitoriumui],
CurrentItem = "Už auditą", [Už auditą],
CurrentItem = "Už kitas administravimo išlaidas", [Kitos veiklos išlaidos],
CurrentItem = "Nekintamas dydis", [Už valdymą2],
CurrentItem = "Visų išlaidų suma", [Visų išlaidų suma],
CurrentItem = "Bendrų išlaidų suma ir koeficientas", [Bendrųjų išlaidų suma ir koeficientas],
0)

 

For some reason, the subtotal measureas (or any measures that use other measures that are mentioned in this DAX formula) appear on the table as zeros, even though they are calculating correctly in separate visuals:

almafater2_0-1623752666449.png

Attaching example link to example file:
https://www.dropbox.com/s/lu57ebyljvz6r1c/Example.pbix?dl=0

What am I missing here? Any help appreciated. 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thank you for the responce. I managed to solve the problem myself. It turned out that the problem was, that there was a active relationship between colum income Statement Map'[Atsiskaitymai] and And Mapping[Mapping II]

View solution in original post

3 REPLIES 3
v-angzheng-msft
Community Support
Community Support

May I ask if your problem has been solved? Is the above answer helpful to you?
If it helps, could you please mark the response which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

amitchandak
Super User
Super User

@Anonymous , You need force some group by

Per laikotarpį priskaičiuoti atskaitymai =
VAR CurrentItem = SELECTEDVALUE('Income Statement Map'[Atsiskaitymai])
RETURN
sumx(values('Income Statement Map'[Atsiskaitymai]),
SWITCH(TRUE(),
CurrentItem = "Kitos veiklos išlaidos", [Kitos veiklos išlaidos],
CurrentItem = "Už apskaitos paslaugas", [Už apskaitos paslaugas],
CurrentItem = "Už teisines paslaugas", [Už teisines paslaugas],
CurrentItem = "Už valdymą", [Už valdymą],
CurrentItem = "Sėkmės mokestis", [Sėkmės mokestis],
CurrentItem = "Už gautų paskolų palūkanas", [Už gautų paskolų palūkanas],
CurrentItem = "Už depozitoriumo paslaugas", [Depozitoriumui],
CurrentItem = "Už auditą", [Už auditą],
CurrentItem = "Už kitas administravimo išlaidas", [Kitos veiklos išlaidos],
CurrentItem = "Nekintamas dydis", [Už valdymą2],
CurrentItem = "Visų išlaidų suma", [Visų išlaidų suma],
CurrentItem = "Bendrų išlaidų suma ir koeficientas", [Bendrųjų išlaidų suma ir koeficientas],
0))

Anonymous
Not applicable

Thank you for the responce. I managed to solve the problem myself. It turned out that the problem was, that there was a active relationship between colum income Statement Map'[Atsiskaitymai] and And Mapping[Mapping II]

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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