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

Wrong Sub Total

Hi All, 

 

i am having trouble getting correct subtotal in the unit price calculation shown in the picture. The numbers below the subtotal works fine but the total is wrong it should have been 5,856,787 instead of the current 9,898,119. The measuer i sued to calculate is pretty straight forward which is:

Unit Price = ([LE3(Price)]-[Actual(Price)])*[Vol. Brewed Actual 2]*[Actual (con. per hl)]

 

and unit price depends on the following measures:

 

1.LE3(Price) = DIVIDE([LE3],[LE3(per unit)])

2.Actual(Price) = DIVIDE([Actual],[Actual(per unit)])

3.Volume Brewed LE2 = CALCULATE(SUM('FCT_Wort Volume'[Volume in (hl)]),'FCT_Wort Volume'[Version]="LE3",'FCT_Wort Volume'[PLLI Description]="Volume Brewed",ALL('Raw Materials'[Item Description]))

4.Actual (con. per hl) = DIVIDE([Actual(per unit)],[Vol. Brewed Actual 2])*-1

5. LE3 = CALCULATE(SUM('FCT_Raw Material'[Amount]),'FCT_Raw Material'[Version]="LE3")

6. LE3(per unit) = CALCULATE(SUM('FCT_Raw Material'[Quantity],'FCT_Raw Material[Version]="LE3")
7. Actual = CALCULATE(SUM('FCT_Raw Material'[Amount]),'FCT_Raw Material'[Version]="Actual",'FCT_Raw Material'[Type ]="Raw Material",'FCT_Raw Material'[Entry Type]="Consumption")


 

Capture.JPGThe file only uses one table which is why i didn't need a table reference in most of the calculations.

 

Please help me in fixing the subtotal for the unit price.

 

KR,

 

Ermias

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

Hi @Anonymous ,

 

It seems like a common measure calculation issue on total level.(when measure calculated on total level, it will direct calculated with summary row contents instead of calculate drill to detail row and summary calculation result)

 

For example:
Detail level: 2*3,3*4,5*6
Total level: (2+3+5)*(3+4+6)

14.png

 

You can take a look at following link to know more about this:

Measure Totals, The Final Word

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

Hi @Anonymous ,

 

It seems like a common measure calculation issue on total level.(when measure calculated on total level, it will direct calculated with summary row contents instead of calculate drill to detail row and summary calculation result)

 

For example:
Detail level: 2*3,3*4,5*6
Total level: (2+3+5)*(3+4+6)

14.png

 

You can take a look at following link to know more about this:

Measure Totals, The Final Word

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.