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
Thigs
Helper III
Helper III

Selected Value Times Each Row?

Hi all

I am trying to create some kind of formula that would work based on a selected value/what if parameter. My what if parameter is how many days of stock I want to have on hand - it is whole numbers from 1 - 60. 

 

Then I have created a summary table that has Item ID, Month, Average items sold per day, and total items sold during that month. I want to create a formula that multiplies the average number of items sold per day by the stock days on hand (what if parameter). Here is what I've tried - 

Inventory Units =
VAR InternalTable = summarize('Order Data','Order Data'[ITEM_ID],'Order Data'[Month], "Units per Day", sum('Order Data'[ORIG_ORDER_QTY])/DISTINCTCOUNT('Order Data'[Date Time]), "Units per Month", sum('Order Data'[ORIG_ORDER_QTY]))

VAR InvUnits = sumx(InternalTable, [Units per Day] * SELECTEDVALUE('Days of Inventory'[Days of Inventory]))

RETURN
InvUnits

This is just returning blanks for each row. Any help would be greatly appreciated!
1 ACCEPTED SOLUTION

@Thigs 

Try

Inventory Units =
DIVIDE (
    SUM ( 'Order Data'[ORIG_ORDER_QTY] ),
    DISTINCTCOUNT ( 'Order Data'[Date Time] )
)
    * SELECTEDVALUE ( 'Days of Inventory'[Days of Inventory] )

View solution in original post

7 REPLIES 7
tamerj1
Super User
Super User

@Thigs 

What is the relationship between the two tables? How does your visual look like?

Between which two tables? The summary table? Right now I only have the one table and the what-if parameter, which I have not connected to anywhere. The Days of Inventory is a what-if parameter that simply contains numbers between 1-60. 

@Thigs 

That is strage. Must be something in the visual. But ofcaurse you select only value right?  Can you please share a screenshot 

I officially got it to not be blank, but now it's all the same value -

 

 

Screenshot What If.PNG

 

@Thigs 

Try

Inventory Units =
DIVIDE (
    SUM ( 'Order Data'[ORIG_ORDER_QTY] ),
    DISTINCTCOUNT ( 'Order Data'[Date Time] )
)
    * SELECTEDVALUE ( 'Days of Inventory'[Days of Inventory] )

I think this worked! Double checking it now manually to make sure, but it looks good!

Hi @Thigs ,

 

May I ask if your problem has been solved? Did tamerj1's post above help you? If any of the posts are helpful, then please consider accepting it as a solution to help other members find it faster. Many thanks!

 

Best Regards,
Community Support Team_Gao

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.

Top Solution Authors