Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi All,
i´m struggeling to create a Costcenter Report, where some kind of consolidation has to be done.
Attached you can see some sample data.
What i want to achieve is: If a User selects a Costcenter - he should see the Amount of costs.
If a User selects two Costcenter - there are some costs which are transferred between these 2.
For Example a user Filters on the Costcenter: 43960654 and Costcenter 43960552.
He should see all rows, where the specific costcenter is in Column "COSTCENTER".
In Addition to this the user should see all other rows, where the COSTCENTER is blank, but the column "CONSOLIDATION" has both Costcenters included: in this case: 43960654-43960552 or 43960552-43960654 (all Rows with a red boarder).
I tried a lot but i am unable to create a measure and i´m even not sure if this can work?
I hope anyone can help me?
Best Regards!
Hi Xiaoxin,
unfortunately i was still not able to create the measure we need.
I still doubt that it is even possible 😕
Additionally i could not upload any sample figures within this thread - i just received errors.
Therefore i´ve recreated the thread and posted the sample data insight.
We are still looking for a solution and i really hope that you can help.
Thanks already and best regards.
-- Here is the link with the sample data --
Filter on multiple Columns / Values - Data Consoli... - Microsoft Fabric Community
Hi all,
unfortunately i was still not able to find a solution.
Maybe it´s even not possible to create such a measure.
I still hope that someone can help us for this topic.
I´ve uploaded some sample data.
Best Regards
COSTCENTER | AMOUNT | MONTH | SOURCEOBJECT | COSTELEMENT | CONSOLIDATION | HIERARCHY |
43960654 | 92531,88 | 1 | 43960552 | 6600250000 | TOTAL_COSTS | |
43960654 | 52374,15 | 1 | 43960551 | 6600250000 | TOTAL_COSTS | |
43960654 | 406388,97 | 1 | 6600260000 | TOTAL_COSTS | ||
43960552 | 736 | 1 | 43960654 | 6520000001 | TOTAL_COSTS | |
43960552 | 684,54 | 1 | 43960655 | 6520000016 | TOTAL_COSTS | |
43960552 | 91111,46 | 1 | 4020110000 | TOTAL_COSTS | ||
-92531,88 | 1 | 43960552 | 8888888888 | 43960654-43960552 | TOTAL_COSTS | |
92531,88 | 1 | 43960552 | 9999999999 | 43960654-43960552 | CHARGES | |
-52374,15 | 1 | 43960551 | 8888888888 | 43960654-43960551 | TOTAL_COSTS | |
52374,15 | 1 | 43960551 | 9999999999 | 43960654-43960551 | CHARGES | |
-736 | 1 | 43960654 | 8888888888 | 43960552-43960654 | TOTAL_COSTS | |
736 | 1 | 43960654 | 9999999999 | 43960552-43960654 | CHARGES | |
-684,54 | 1 | 43960655 | 8888888888 | 43960552-43960655 | TOTAL_COSTS | |
684,54 | 1 | 43960655 | 9999999999 | 43960552-43960655 | CHARGES |
HI @Quakker,
You can try to use the following measure formula to get the different based on current COSTCENTER group:
formula =
VAR currCostcenter =
SELECTEDVALUE ( Table[COSTCENTER] )
RETURN
IF (
currCostcenter = BLANK (),
SUM ( Table[Amount] ),
VAR _path =
SUBSTITUTE ( currCostcenter, "-", "|" )
RETURN
CALCULATE (
SUM ( Table[Amount] ),
FILTER ( 'Table', Table[SOURCEOBJECT] & "" = PATHITEM ( _path, 1 ) )
)
- CALCULATE (
SUM ( Table[Amount] ),
FILTER ( 'Table', Table[SOURCEOBJECT] & "" = PATHITEM ( _path, 2 ) )
)
)
Regards,
Xiaoxin Sheng
Hi Xiaoxin,
i just tried your sultion but it doesn´t seem to work.
Above you can see the results within Power BI. There are Costs about 643.827.
My expectation is that there are TOTAL Costs about 550.560 and CHARGES about 93.267
Do you know whats wrong?
Best regards and already thanks for your help!
Hi @Quakker,
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi Xiaoxin,
we were still not able to find a solution.
Currently we are not sure if it is even possible to create such a measure.
I´ve attached some sample data. Unfortunatley it was not possible to upload it as a table.
Already thanks for your help and best regards!
COSTCENTER;AMOUNT;MONTH;SOURCEOBJECT;COSTELEMENT;CONSOLIDATION;HIERARCHY
43960654;92531,88;1;43960552;6600250000;;TOTAL_COSTS
43960654;52374,15;1;43960551;6600250000;;TOTAL_COSTS
43960654;406388,97;1;;6600260000;;TOTAL_COSTS
43960552;736;1;43960654;6520000001;;TOTAL_COSTS
43960552;684,54;1;43960655;6520000016;;TOTAL_COSTS
43960552;91111,46;1;;4020110000;;TOTAL_COSTS
;-92531,88;1;43960552;8888888888;43960654-43960552;TOTAL_COSTS
;92531,88;1;43960552;9999999999;43960654-43960552;CHARGES
;-52374,15;1;43960551;8888888888;43960654-43960551;TOTAL_COSTS
;52374,15;1;43960551;9999999999;43960654-43960551;CHARGES
;-736;1;43960654;8888888888;43960552-43960654;TOTAL_COSTS
;736;1;43960654;9999999999;43960552-43960654;CHARGES
;-684,54;1;43960655;8888888888;43960552-43960655;TOTAL_COSTS
;684,54;1;43960655;9999999999;43960552-43960655;CHARGES
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
127 | |
108 | |
87 | |
70 | |
66 |