Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
baijumohan1990
Helper II
Helper II

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

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:


1.PNG

 

 

 

 

Expected Data: 

 

2.PNG

 

 

 

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

 

6 REPLIES 6
harshnathani
Community Champion
Community Champion

HI @baijumohan1990 ,

 

 

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

 

 

1.jpg

 

 

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

harshnathani
Community Champion
Community Champion

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)

Hi, 

 

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

v-jayw-msft
Community Support
Community Support

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.

2.PNG3.PNG 

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Ajinkya369
Resolver III
Resolver III

Hi @baijumohan1990 ,

 

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

 

Thank you

apologies...edited the question..

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.