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

Sum/Ratio problem

I have location I am trying to set a min and max up for a shipping schedule.  If filtered for a single day, everything looks fine but when include multiple days (like 5 days as shown in table below), start seeing inbound as a ratio of "Actual Total".

For example, Actual Daily Min top line has 19 for daily min, but over a 5 day period it multiplying 19 * 60 (or the Actual Total) to get 1140 where I just want it to be : 19 *5  . In this example I want the total to reflect 19*5 or 95.

 

I have a lookup value for the Actual Daily Min that inputs a value for every row in my table so I imagine I need to create a measure to accomplish my 19 *5 or whatever total based on date range.  That is where I am stuck.

 

Thank you for looking and appreciate any guidance you can provide

 

Sum Ratio 2.PNG

4 REPLIES 4
Cmcmahan
Resident Rockstar
Resident Rockstar

What are the current measures you're using?  I'm most interested in how you're calculating [Actual Total] and [Incorrect 5 day Min Inbound]

Anonymous
Not applicable

Actual Total is a distinct count of shipment numbers

 

Incorrect 5 day Min Inbound is from a Lookup Value from a static table  that will be updated monthly

So then wouldn't the fix be updating your static table with the correct values?

Anonymous
Not applicable

I don't believe so because it will change depending on how it is filter; day, week, month. 

 

It is setup in the static table to be daily. 

 

I believe I need to create a measure to avoid the calculated column, row by row taking the lookup value found in the static table and multipling by number of loads. 

 

For example: I want it to calculate 5 days times 4 loads minimum a day for total of 20 loads in a 5 day period but it is taking 4 load minimum per day and multiplying by 9 loads(since 9 loads actually shipped over the 5 day period).

 

 

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.