Forum Discussion
Advocate IProfit loss calculation report in Matrix Visual
Coming to think about it, there is actually a simpler solution:
GuV = if([IsDetail],[Act],[RT])
with
IsDetail = ISFILTERED('Kostenart zu DB-Zeile'[Konto ++]) || ISFILTERED('Kostenart zu DB-Zeile'[DB Unterzeile ++]) || ISFILTERED('Kostenart zu DB-Zeile'[DB Zeile ++])The measure "IsDetail" checks if one of the levels/groups that are considered as details is filtered. No need to adjust any tables then :)
Resident RockstarHi Alex_Berlin,
Based on your detail description, I understand that you want to get the output like Figure 2?
However, It's hard for me to reproduce your scenario, because I don't know your table structure.
If it is convenient, could you share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)
Best Regards,
Cherry
Advocate IHi Cherry,
yes, the output shoud look like Figure 2.
You will find the pbix and the excel data file under the following link to onedrive:
https://1drv.ms/f/s!Aiwh2CWXTd4jiL8_AzqK7WwMKchjrA
I hope it will be no problem, that everything is in german. If however give me a sign and I will try to translate the wording. Please note, that in Germany Power BI uses the ";" in lieu of the "," in formulas.
Thank you very much in advance! for trying to help me!
Best regards from Berlin, Germany
Alex
- ImkeF
Community Champion
Hi Alex,
for your specific scenario, you can use a GuV-Measure like this:
GuV = if([ZeilenCat]=[ZeilenContext],[RT],[Act])
where
ZeilenCat = CALCULATE(COUNTROWS('Kostenart zu DB-Zeile'), FILTER(ALL('Kostenart zu DB-Zeile'), 'Kostenart zu DB-Zeile'[DB Summenzeile ++]=MAX('Kostenart zu DB-Zeile'[DB Summenzeile ++]))) ZeilenContext = COUNTROWS('Kostenart zu DB-Zeile') Act = SUM('Daten fixiert'[Betrag (korr. VZ)]) RT = IF(NOT(ISBLANK(SUM('Daten fixiert'[Betrag (korr. VZ)]))),CALCULATE(SUM('Daten fixiert'[Betrag (korr. VZ)]), FILTER(ALL('Kostenart zu DB-Zeile'), 'Kostenart zu DB-Zeile'[DB Summenzeile]<=MAX('Kostenart zu DB-Zeile'[DB Summenzeile]))))So the running total (RT) is used on subtotals and the actual figures (Act) on detail level. The identification if we are on a subtotal or not is determined by a comparison of the amount of rows filtered.
This works fine with one exemption: "Ergebnis der gewöhnlichen Geschäftstätigkeit" (the last item) where there is only one subcategory within the main category. You can easily bypass this by adding a "Dummy-row" with a different value in "DB-Unterzeile" into your account-group-table (so that the number of accounts within the sub-levels are lower than alll accounts on the group-level). (I will try to find out if there are different methods to achieve this that don't require this workaround and post here)
Link to file: https://1drv.ms/u/s!Av_aAl3fXRbehbIatilKWOIHSvoGYA
- ImkeF
Coming to think about it, there is actually a simpler solution:
GuV = if([IsDetail],[Act],[RT])
with
IsDetail = ISFILTERED('Kostenart zu DB-Zeile'[Konto ++]) || ISFILTERED('Kostenart zu DB-Zeile'[DB Unterzeile ++]) || ISFILTERED('Kostenart zu DB-Zeile'[DB Zeile ++])The measure "IsDetail" checks if one of the levels/groups that are considered as details is filtered. No need to adjust any tables then :)
- Alex_Berlin
Hi Imke,
thank you very much! This works!
:smileyhappy:
Alex