Include record in total calculation but not listed in visualisation as individual row

[Anonymous]

Hi,

On our monthly reporting we include a Gender Pay Gap breakdown. I want to include our Exec members in the overall company calculation which is the 'Total' field but not in the divisional breakdowns. As you can see in the image below teh first row (100%) is the Exec member singled out with no division listed. If I hide the division using the filters it excludes them from the overall total %. Is there a way I can include in total but not have listed in visualisation?

[MFelix · Accepted solution]

Hi @Anonymous,

Using the IF statment will work as in Excel so in this case since you didn't put any 3rd parameter the value is blank in total you need to redo your measure to this:

Test Measure =
IF (
    HASONEVALUE ( 'Aug Rem Report'[Division Code] ),
    CALCULATE ( [August GPG] ),
    CALCULATE ( [August GPG], ALL ( 'Aug Rem Report'[Division Code] ) )
)

Filter out the first Divison code from your table

I'm assuming that August GPG is a measure. The explanation of the measure is as follow:

HASONEVALUE = checks if the Division Code exists if so returns the August GPG measure

CALCULATE / ALL = this is used when the context of Division  doesn't exists (total line) and it calculates the GPG but the ALL function removes the filter context and adds all the division to the calculation.

So the last line is calculate in a different way from the rest of the lines,

Regards,

MFelix

[MFelix]

Hi @Anonymous,

You need to had something like the measure below:

Measure =
IF (
    HASONEVALUE ( Table1[Cat] );
    MAX ( Table1[Value] );
    CALCULATE ( AVERAGE ( Table1[Value] ); ALL ( Table1[Cat] ) )
)

Replace the MAX and AVERAGE measure by the formulas you need.

Regards,

MFelix

[Anonymous]

Thanks @MFelix.

Below is the visualisation - I am trying to remove/hide blank 'Division' but include that individual in the total if it makes sense. 

The equation I have to pull through test Measure is as below:

Test Measure = IF (
HASONEVALUE('Aug Rem Report'[Division Code]), CALCULATE('Aug Rem Report'[August GPG]))

Any ideas or help would be ACE!

[MFelix · Accepted solution]

Hi @Anonymous,

Using the IF statment will work as in Excel so in this case since you didn't put any 3rd parameter the value is blank in total you need to redo your measure to this:

Test Measure =
IF (
    HASONEVALUE ( 'Aug Rem Report'[Division Code] ),
    CALCULATE ( [August GPG] ),
    CALCULATE ( [August GPG], ALL ( 'Aug Rem Report'[Division Code] ) )
)

Filter out the first Divison code from your table

I'm assuming that August GPG is a measure. The explanation of the measure is as follow:

HASONEVALUE = checks if the Division Code exists if so returns the August GPG measure

CALCULATE / ALL = this is used when the context of Division  doesn't exists (total line) and it calculates the GPG but the ALL function removes the filter context and adds all the division to the calculation.

So the last line is calculate in a different way from the rest of the lines,

Regards,

MFelix

[Anonymous · Accepted solution]

I ended up using the below. 

I filtered out the division that I assigned to that individual.

September = CALCULATE(('Sep Rem Report'[Sep GPG]),ALL('Division - Link'[Division]))

Thanks for your help!