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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Drobinson1
Helper III
Helper III

Weighted Average of two measures

I have two measures Rawqty used and raw material usedweight by warehouse.  They work great when I have warehouse and Part_0 in each row.  However I want to remove those field and only have Raw Material and RawMaterialUsedWeightbyWarehousepartcode measure showing.  

 

Basically just a total of the Rawmaterialusedweightbywarehousepartcode column.  Since teh qtyused is a measure that averages based on part_0 and warehouse, once these columns are removed my data calculates incorrrectly.

 

 

 

 

Capture.JPG

 

1 ACCEPTED SOLUTION
jahida
Impactful Individual
Impactful Individual

Ok I lied, I don't want to try to understand that formula. Can you try:

 

NewMeasure = SUMX(SUMMARIZE('Table', 'Table'[Warehouse], 'Table'[part_0], "num", [RawMaterialUsedweightbywarehousepartcode]), [num])

 

?

View solution in original post

9 REPLIES 9
chrisu
Responsive Resident
Responsive Resident

To retain the row context without explicitly showing the rows in the table, you can use one of the "X" functions.  In your case, if you want to show the total, you would use SUMX and tell it to iterate over the Part # while calculating RawMaterialUsed...  

 

See http://www.powerpivotpro.com/2010/02/sumx-the-5-point-palm-exploding-fxn-technique/ for more information.  A relevant line from that post:

 

"The fields referenced in SUMX do NOT have to be present in your [table]...I like to think of it as a stack of invisible cells underneath each pivot cell that you can see, and SUMX is rolling up a lot of logic across those invisible cells to return a simple number to the top cell you can see."

 

CheenuSing
Community Champion
Community Champion

Hi @Drobinson1

 

Can you share the sample data and the final output you desire.

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

The screen shot in the orginal attachement had the sample data that has the correct amounts when broken out by warehouse and part_0.

 

 

 

I expect the result to be 67,344 which is the sum of the last column.

 

However when I remove Part_0 and ware house my totals change, due to measure qty used2 being a calculated average

jahida
Impactful Individual
Impactful Individual

The formulas you're currently using for the measures would be useful for understanding I think. Although conceptually, I think @chrisu is right about the solution.

Rawqtyused2=calculate(average(Rc_Full_Level_Recipes[Rawqtyused]) RawMaterialUsedweightbywarehousepartcode = IF([RawQTYUsed2]=1,CALCULATE(SUM(RC_Daily_Sales_Report_Invoice_Line_Detail[Invoice_Qty]),FILTER(ALL(RC_FULL_LEVEL_RECIPES[Warehouse_0]),COUNTROWS(FILTER(VALUES(RC_Daily_Sales_Report_Invoice_Line_Detail),RC_Daily_Sales_Report_Invoice_Line_Detail[Warehouse]=RC_FULL_LEVEL_RECIPES[Warehouse_0]))>0),FILTER(ALL(RC_FULL_LEVEL_RECIPES[Part_0]),COUNTROWS(FILTER(VALUES(RC_Daily_Sales_Report_Invoice_Line_Detail),RC_Daily_Sales_Report_Invoice_Line_Detail[PART_CODE]=RC_FULL_LEVEL_RECIPES[Part_0]))>0))*[RawQTYUsed2],CALCULATE(SUM(RC_Daily_Sales_Report_Invoice_Line_Detail[Weight]),FILTER(ALL(RC_FULL_LEVEL_RECIPES[Warehouse_0]),COUNTROWS(FILTER(VALUES(RC_Daily_Sales_Report_Invoice_Line_Detail),RC_Daily_Sales_Report_Invoice_Line_Detail[Warehouse]=RC_FULL_LEVEL_RECIPES[Warehouse_0]))>0),FILTER(ALL(RC_FULL_LEVEL_RECIPES[Part_0]),COUNTROWS(FILTER(VALUES(RC_Daily_Sales_Report_Invoice_Line_Detail),RC_Daily_Sales_Report_Invoice_Line_Detail[PART_CODE]=RC_FULL_LEVEL_RECIPES[Part_0]))>0))*[RawQTYUsed2])
jahida
Impactful Individual
Impactful Individual

Ok I lied, I don't want to try to understand that formula. Can you try:

 

NewMeasure = SUMX(SUMMARIZE('Table', 'Table'[Warehouse], 'Table'[part_0], "num", [RawMaterialUsedweightbywarehousepartcode]), [num])

 

?

Any thoughts on ignoring a customer filter using that example. 

 

So in the example below we use 83,000,000 lbs of a product as a company.  But when we select a specific customer it filters correctly to show that customer uses 108,969.  I would like to have a second meaasure in that table that when a customer it still shows to total company usage. This way we can quickly say the customer uses 108,969 or the 83,000,000 lbs.

Capture1.JPGCapture2.JPG

jahida
Impactful Individual
Impactful Individual

Just wrap the measure I gave above in a calculate with an ALL on the filter you'd like to ignore.

 

SecondMeasure = CALCULATE([NewMeasure], ALL('Table'[Customer]))

that doesnt seem to work. Everytime I select a customer I get a different "all" number for the same raw material.

 

I have also tryed using several different variations of all between the three tables still with no luck.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.