Exclude a Value only from Total and Show it in Details.

[baijumohan1990]

Hi,

I have a requirement to exclude a value only from the total while keeping it visible in the details in a power bi report.  Below is the example : "Free" value in the source column needs to be displayed in the details row..not in the total.

Current Data:

Expected Data: 

                                                                                                                     When I put filter of the Sum measure its removing the details also. Any help much appreciated.

[harshnathani]

HI @baijumohan1990 ,

New Sales Measure without Free = IF (MAX('Table'[Source]) = "Free", SUM('Table'[New Sales]),CALCULATE([New Sales Measure],KEEPFILTERS('Table'[Source] <> "Free") ))

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[esulyma]

Thanks for this solution, you're a lifesaver!

[harshnathani]

HI @baijumohan1990 ,

Measure 2 = CALCULATE(SUM(Table1[New Sales]), Table1[New Source] <> "Free")

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[baijumohan1990]

Hi, 

Adding a filter removes the data from the detail also..i need to excluded only from total..

[Anonymous]

Hi @baijumohan1990 ,

You could create a new table contains "source" column as slicer and create measrues as below.

slicer = DISTINCT('Table'[source])

sum_opening base = CALCULATE(SUM('Table'[opening base]),FILTER(ALL('Table'),'Table'[source]<>SELECTEDVALUE('slicer'[source])))

sum_new sales = CALCULATE(SUM('Table'[new sales]),FILTER(ALL('Table'),'Table'[source]<>SELECTEDVALUE('slicer'[source])))

Result would be shown as below.

 

Best Regards,

Jay

[Ajinkya369]

Hi @baijumohan1990 ,

I dont find any difference between the current and expected data.Please be more clear.

Thank you

[baijumohan1990]

apologies...edited the question..