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
NoordenNL076
Regular Visitor

Need help with measure total

Hello,

 

I need help with the following problem:

 

I'm calulating a MAX. number of products. The measure is calculating the total row of the table, instead of per row. The final sum should be around 200k, but it is only showing 97k

 

The measure is:

 

New Max QTY = iferror(IF((iferror(ROUNDUP(('Calculations'[Avg daily boxes all dates]*'Days between replenishment'[Days between replenishment Value]+'Calculations'[New Min QTY])/AVERAGE(MLGT[Replen.qty]),0),AVERAGE(MLGT[Replen.qty])))*AVERAGE(MLGT[Replen.qty])>1,
 
(ROUNDUP(('Calculations'[Avg daily boxes all dates]*'Days between replenishment'[Days between replenishment Value]+'Calculations'[New Min QTY])/AVERAGE(MLGT[Replen.qty]),0))*AVERAGE(MLGT[Replen.qty]),
 
(ROUNDUP(('Calculations'[Avg daily boxes all dates]*'Days between replenishment'[Days between replenishment Value]+'Calculations'[New Min QTY])/AVERAGE(MLGT[Replen.qty]),0))*AVERAGE(MLGT[Replen.qty])+AVERAGE(MLGT[Replen.qty])), AVERAGE(MLGT[Replen.qty]))

 

An example on the problem:

 

The right calculation needs to be:

 

(10.68*5+104)/10 = 15.74 > Roundup to 16

16*10 = 160

 

Over 3 rows, the SUM should be: 127, but PowerBI is calculating the above formula over the totals of the table resulting in a false value of 102.

 

Voorbeeld_powerBI_probleem.PNG

 

Can someone please help with this?

 

Thank you!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@NoordenNL076 , Based on the not summarized column in the

you need to have a measure like

 

sumx(summarize(Fact, Dim[Column1], "_1",[New Max QTY]), [_1])

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@NoordenNL076 , Based on the not summarized column in the

you need to have a measure like

 

sumx(summarize(Fact, Dim[Column1], "_1",[New Max QTY]), [_1])

It worked. You're a hero!

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.