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
RomainTo
Frequent Visitor

Calculate Sum with filter

Hello,

 

I am facing an issue with a basic measure I want to create :

 

I have a dimension table that have two values :

Y

N

 

This table is linked (1,n) to my fact table that have a X amount

 

I want a measure that gives the total of X for Y only :

 

-> CALCULATE(SUM(X), DIM_TABLE = Y)

 

The total given is good, but as soon as I cross this measure with the dimension the result is :

 

Y | 1000

N | 1000

Total | 1000

 

But I would expect :

 

Y | 1000

N | 0 (since the measure is filtered on DIM = Y)

Total | 1000

 

Could you tell me how to fix it ? 

Thanks.

 

Romain

4 REPLIES 4
VijayP
Super User
Super User

@RomainTo 

I think some sample data / pbix without any classified information is helpful to give right solution




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Yes you're right 🙂

 

So I have this :

 

RomainTo_0-1666789956392.png

 

I want a measure that returns only 3 565 169 673 195 on the NON_MNI row.

 

I have the following measure 

 

Top_SRN_WithBPCE_Conso_SRN = CALCULATE([Consommation_1],FILTER(ALL('Dimension Convention'[top_mni]),'Dimension Convention'[top_mni]="NON_MNI"))
 
And i have all the duplicates on the all rows.... I don't understand why. It should be null for other rows.
 
Thanks !
RomainTo
Frequent Visitor

Hello VijayP, thanks for the proposal but same issue 😞

 

And when I take another field from another dimension, or another field from the same dimension this measure is working...

So it seems to be wrong as soon as I want to show the measure with the same field I am filtering on...

VijayP
Super User
Super User

Hi @RomainTo  try this!

CALCULATE(SUM(X), filter(all(dimtable[column]),DIM_TABLE = Y)




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


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.