Solved

By Sum and filter


Userlevel 3
Badge +6
  • Newly Minted Pigmenteer
  • 21 replies

Hello! 

 

I am trying to summarize the cost by department and division by monthly basis using transaction list from another app. The setup is set as how I would like to see but the sum of amount is not same as what I expect. Can you please help? Thanks in advance!

 

This is the logic I used to create a metric:

'PL GL Data'.'Net Amount (USD)'[BY SUM:'PL GL Data'.'PL L0','PL GL Data'.Division,'PL GL Data'.Department,'PL GL Data'.Month][FILTER:'PL GLs'.'PL Cat 1'='PL Cat 1'."Headcount Costs"]

Here’s the PL GL Data transaction list. For sanity check, I filtered out “Headcount Cost” for PL Cat 1 and March 2023 transactions only. Total should be $9,100,788.45 but I am getting $9,124,046.48


 

 

 

icon

Best answer by Yaejis 9 May 2023, 18:08

View original

4 replies

Userlevel 3
Badge +6

Unsure how to delete this question. It’s solved! 

Userlevel 6
Badge +12

Hi Yaejis,

Thank you for posting your question. We’re glad you resolved it!

If you’re happy to share how you solved it, I’m pretty sure it would help other members facing the same situation in the future.

Thanks,
Benoit

 

Userlevel 6
Badge +11

Hi Yaejis,

 

Two things I’d like to highlight:

  • the fact that it is from a shared app has no impact
  • I’d suggest you do the aggregation metric in the source app, including all the type of costs, and reference that metric in your other apps instead of re-aggregating from the TL every time. Best for a clean logic & performance
Userlevel 3
Badge +6

Hi team, 

 

@Benoit , it was because there were few transactions that were not mapped to the one of the department! 

Thanks for suggestion @Nathan!

 

 

Reply