Forum Discussion

Quakker's avatar
Quakker
Helper II
3 years ago
Solved

Filter on multiple Columns / Values - Data Consolidation - Sample Data

 

Filter on multiple Columns / Values - Data Consolidation
‎06-06-2023 12:05 PM

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!

 

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
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Quakker ,

     

    I suggest you to try to create a measure to filter your visual.

    Filter = 
VAR _SELECT = VALUES(DimCostcenter[COSTCENTER])
RETURN
IF(MAX('Table'[COSTCENTER]) IN _SELECT || MAX('Table'[SOURCEOBJECT]) IN _SELECT,1,0)

    Add this measure into visual level filter and set it to show items when value =1.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Quakker ,

     

    I suggest you to try to create a measure to filter your visual.

    Filter = 
VAR _SELECT = VALUES(DimCostcenter[COSTCENTER])
RETURN
IF(MAX('Table'[COSTCENTER]) IN _SELECT || MAX('Table'[SOURCEOBJECT]) IN _SELECT,1,0)

    Add this measure into visual level filter and set it to show items when value =1.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.