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
Anonymous
Not applicable

FIlter Conect > Measure that calculates delta based on filter

Hi,

 

I have a question that feels very simple but I just can't work it out.

 

My data table looks a little like this:

CountryProductCategoryLocationQuantityCost
ItalyHelmetIn Store622
GermanyGlovesIn Store850
FranceGlovesIn Store221
SpainShirtIn Store925
ItalyHelmetRoad Show622
GermanyShirtRoad Show944
FranceHelmetRoad Show425
SpainHelmetRoad Show227
ItalyShirtIn Store735
GermanyHelmetIn Store645
FranceHelmetIn Store1041

 

I've created a measure that calculates the cost per product via CostPerProduct =

DIVIDE(
    SUM(Cost),
    SUM(Quantity),
    0
)

 

What I want to do is create a measure that calculates the difference between the CostPerProduct for "In-Store" and "Road Show" locations. I thought the following measure would help me with this:

CostPerProductDelta =

CALCULATE(
   DIVIDE(
      SUM(Cost),
      SUM(Quantity),
      0
   ),
   Table[Location]="In-Store",
   ALL(Table[Location])
) - 
CALCULATE(
   DIVIDE(
      SUM(Cost),
      SUM(Quantity),
      0
   ),
   Table[Location]="Road Show",
   ALL(Table[Location])
)

Now, I would assume htat this would show me the difference between the CostPerProduct for In-Store and Road Show locations. Unfortunately, it sometimes shows a '-Infinity' value for CostPerProductDelta when I set a page level filter to 'In-Store'.

 

What am I missing here?

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

If you want to calculate the cost per product per country,

please create measures as below

In Store = CALCULATE(SUM(Table1[Cost])/SUM(Table1[Quantity]),FILTER(ALLEXCEPT(Table1,Table1[Country],Table1[ProductCategory]),Table1[Location]="In Store"))

Road Show = CALCULATE(SUM(Table1[Cost])/SUM(Table1[Quantity]),FILTER(ALLEXCEPT(Table1,Table1[Country],Table1[ProductCategory]),Table1[Location]="Road Show"))

difference = [In Store]-[Road Show]

6.png

For some line, there is no data for these two measures, to see these, you need to click on any column from the table and enable "show data with no data".

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

If you want to calculate the cost per product per country,

please create measures as below

In Store = CALCULATE(SUM(Table1[Cost])/SUM(Table1[Quantity]),FILTER(ALLEXCEPT(Table1,Table1[Country],Table1[ProductCategory]),Table1[Location]="In Store"))

Road Show = CALCULATE(SUM(Table1[Cost])/SUM(Table1[Quantity]),FILTER(ALLEXCEPT(Table1,Table1[Country],Table1[ProductCategory]),Table1[Location]="Road Show"))

difference = [In Store]-[Road Show]

6.png

For some line, there is no data for these two measures, to see these, you need to click on any column from the table and enable "show data with no data".

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi Maggie (@v-juanli-msft),


Thanks for your elaborate answer. This really helps a lot.

I hope you can find the time to take a look at one other small question regarding your response.

 

In the future we will probably increase the number of dimensions in the dataset. I don't really want to add all dimensions except 'Location' to the ALLEXCEPT function. Is it safe to assume that the following formula works as well:

In Store = CALCULATE(SUM(Table1[Cost])/SUM(Table1[Quantity]),FILTER(ALL(Table1[Location]),Table1[Location]="In Store"))

? It appears to yield the same result now, but I'm not sure if this will still be the case when we add dimensions.

 

Thanks in advance!

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.