cancel
Showing results for
Did you mean:
Helper I

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ž 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:

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
Helper I

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]

3 REPLIES 3
Community Support

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.

Super User IV

@almafater2 , 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ž 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))

Proud to be a Super User!

Helper I

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]

Announcements

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.