Forum Discussion

OliverMeyer's avatar
OliverMeyer
Copper Contributor
Jun 02, 2024

How to hide lines in pivot table, that have no value in a certain sum-value-column?

 

Hello everyone,

I hope someone is willing to help me with my Pivot Challenge. The actual question is below.

 

I use a Pivot Table on the following datamodel:

In my Pivot Table I see the spent time on a project. This works fine:

People from Team "Malt" worked on Tasks which belong to Project AAA-11111 and spent 2.13 days. They also worked on Project AAA-11202 and spent 9.88 days.

 

I like to compare those spent days with the estimations given for project AAA-11111. But if I add the estimation to the values I see all projects and not only those team Malt worked on:

For Project AAA-11111 and AAA-11202 the entries are correct:

How can I make Excel continue to filter on the SPENT entries but still show the ESTIMATE sums?

I understand why excel behaves as it does.

 

You can download the file I used in the examples from Dropbox.

If you like to share solutions 🙂 you should be able to upload them into a dropbox folder I created.

 

Thank you for your time reading and thinking about this.

Oliver

excel

3 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jun 02, 2024

    OliverMeyer 

    In any case it's better to use explicit measures instead of implicit ones. Thus for Spent it could be

    Time Spent:=SUM( Billings_Table[SPENT] )

    To calculate Estimate from many to one relationship, we may use virtual relationship in measure

    Time Estimate :=
CALCULATE (
    SUM ( Tasks_Table[ESTIMATE] ),
    TREATAS ( VALUES ( Billings_Table[TASK_ID] ), Tasks_Table[TASK_ID] )
)

    or existing physical one with crossfilter

    Estimated Time :=
CALCULATE (
    SUM ( Tasks_Table[ESTIMATE] ),
    CROSSFILTER ( Billings_Table[TASK_ID], Tasks_Table[TASK_ID], BOTH )
)

    Both work, please check in attached file.

Resources