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

Total meausre problems

Sample PBIX - https://www.dropbox.com/s/vob4ngqfkxt3ss5/TeaSugar_Sample.pbix?dl=0

 

I've read several posts on this, but it's still not working for me. I'm looking at an invoice table with products, costs and dates. I want to take the average price for a product at the earliest date and compare it with the average price for a product on the lastest date.

 

The start price appears OK with this:

 

StartCost = 
VAR datestart = CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Product],'Table'[Date]))
return  CALCULATE(average('Table'[Cost]),FILTER(ALL('Table'),'Table'[Date]=datestart))

 

Same for the latest cost, but obviously with MAX in the variable.

 

The change is OK, showing an increase or decrease on the earliest cost. Percent of total is also OK.

Using the percent of total as a weight, the weighted change figure measure is:

 

Weighted Change = 
if (HASONEFILTER(('Table'[Product])),
[Change]*[Percent of total],
SUMX('Table',[Change]*[Percent of total])
 )

 

And this is where it falls down, just showing 0, rather than -13.87%

Any ideas?

 

Ideally I would like to turn off the table totals and show the total weighted change on a card separate to the table.

 

Source Data

ProductCostDate
Sugar5001/02/2020
Sugar5501/02/2020
Sugar15003/02/2020
Sugar4004/02/2020
Sugar4504/02/2020
Tea10002/02/2020
Tea11002/02/2020
Tea50003/02/2020
Tea9008/02/2020
Tea9508/02/2020

 

Report

ProductStartCostEndCostChangeCostTotal Cost all productsPercent of totalWeighted Change
Sugar52.542.5-19.05%340123527.53%-5.24%
Tea10592.5-11.90%895123572.47%-8.63%
Total52.5092.576.19%12351235100.00%0.00%

 

 

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

Hi @sdb_utd ,

you have to iterate over the VALUES of the Product-column like so:

 

Weighted Change =
if (HASONEFILTER(('Table'[Product])),
[Change]*[Percent of total],
SUMX(VALUES('Table'[Product]),[Change]*[Percent of total])
)

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

5 REPLIES 5
ImkeF
Super User
Super User

Hi @sdb_utd ,

you have to iterate over the VALUES of the Product-column like so:

 

Weighted Change =
if (HASONEFILTER(('Table'[Product])),
[Change]*[Percent of total],
SUMX(VALUES('Table'[Product]),[Change]*[Percent of total])
)

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

sdb_utd
Frequent Visitor

OMG, you would not believe how frustrating this was. Thanks!

I totally believe you!

Have been there so many times as well...

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

sdb_utd
Frequent Visitor

Last one - I'm trying to get the total working for the start and end cost.

In the table, the values shown are the average start costs for each product. For the total I want the total of the start price, eg. in this case the total start cost should be 305 (sugar on 1st Feb and tea on 2nd Feb)

 

I don't seem to be able to get the syntax right.

StartCost = 
VAR datestart = CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Product],'Table'[Date]))
return  
if (HASONEFILTER('Table'[Product]),
CALCULATE(average('Table'[Cost]),FILTER(ALL('Table'),'Table'[Date]=datestart)),
sumx(VALUES('Table'[Cost]),'Table'[Cost]))

Just to be sure (as I find that request a bit strange)

What do you want for Tea?:

1) What shall go into the total: 50 + 55 = 105

2) or actually what you have now: (50 + 55) / 2 = 52.5

 

And if it is 2: Are you sure that you don't want 107.5?

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.