Filter on multiple Columns / Values - Data Consolidation

[Quakker]

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!

[Quakker]

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

[Quakker]

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

[Quakker]

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

[v-shex-msft]

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

[Quakker]

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!

[v-shex-msft]

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

[Quakker]

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