Grand Total excluding data

[Anonymous]

Hi,

I have a table A having Product and Value.

Product   Value

A                55

B                64

C                78

D                99

E                 120

F                 180

I have implemented a formula to calculated Grand Total: Calculate(SUM(value),all(A)) Using this formula I am getting grand total value, however I want to exclude Product D from the grand total, I am not getting the correct syntax here.

Grand Total: 596

I am looking to get Without  D grand Total: 497

Any help.

Thanks,

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can try to use following measure formula to achieve your requirement:

Change Grand Total = 
IF (
    ISINSCOPE ( 'Table'[Product] ),
    SUM ( 'Table'[value] ),
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Product] <> "D" )
    )
)

Regards,

Xiaoxin Sheng

[AlB]

Hi @Anonymous

If I understand correctly what you need,the following measure would suffice:

Measure_Total = SUM(A[Value])

You can place this measure in a card visual on your report and apply a slicer with A[Product]. On the slicer, you can deselect "D" and you will get the sum of all the others.   

[Anonymous]

No this may not work when you calculate percentage of each and every product level. 

[affan]

Hi @Anonymous

You can try the following measure

Without D = CALCULATE(SUM(A[ Value]),FILTER(ALL(A),A[Product ]<>"D"))

I have got the below result 

Total Without D

You can also download the pbix file here

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan