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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Quakker
Helper II
Helper II

Filter on multiple Columns / Values - Data Consolidation

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.

Quakker_1-1686045710998.png

 

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!

 

7 REPLIES 7
Quakker
Helper II
Helper II

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

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

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

COSTCENTERAMOUNTMONTHSOURCEOBJECTCOSTELEMENTCONSOLIDATIONHIERARCHY
4396065492531,881439605526600250000 TOTAL_COSTS
4396065452374,151439605516600250000 TOTAL_COSTS
43960654406388,971 6600260000 TOTAL_COSTS
439605527361439606546520000001 TOTAL_COSTS
43960552684,541439606556520000016 TOTAL_COSTS
4396055291111,461 4020110000 TOTAL_COSTS
 -92531,88143960552888888888843960654-43960552TOTAL_COSTS
 92531,88143960552999999999943960654-43960552CHARGES
 -52374,15143960551888888888843960654-43960551TOTAL_COSTS
 52374,15143960551999999999943960654-43960551CHARGES
 -736143960654888888888843960552-43960654TOTAL_COSTS
 736143960654999999999943960552-43960654CHARGES
 -684,54143960655888888888843960552-43960655TOTAL_COSTS
 684,54143960655999999999943960552-43960655CHARGES

 

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin,

i just tried your sultion but it doesn´t seem to work.

Quakker_0-1686317133316.png

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

 

Quakker_1-1686317557159.png

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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