cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

In Stock by Date

Hi,

 

I am trying to calculate in stock rates as a measure and am having trouble writing the DAX.

 

I have item, store, date, inventory, and approved.

 

I would like to have a measure that has (# of stores with inventory/# of stores approved) by item and by day. Ideally we'd then be able to slice by higher item-category levels or by month to date etc.

 

 

Any help would be appreciated.

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: In Stock by Date

Hi @lworrell,

 

Add fields [item] and [date] (set its type to hierarchy and remove all levels expect for day) into visual. The measure could be similar to:

Measure =
CALCULATE (
    COUNT ( Table[stores] ),
    FILTER ( ALLSELECTED ( Table ), Table[inventory] <> BLANK () )
)
    / CALCULATE (
        COUNT ( Table[stores] ),
        FILTER ( ALLSELECTED ( Table ), Table[approved] = "yes" )
    )

For more advice, please provide sample data and desired result. How to Get Your Question Answered Quickly

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: In Stock by Date

Thank you @v-yulgu-msft for your help!

 

That's definitely progress!

 

I ended up doing the following, which gets my desired result, but I am unable to do visualization filtering based on items/hierarchy. In addition I have a store hierarchy that I would filter by (maps, etc.) The percent is static so just need a little bit more help!!

 

Measure = CALCULATE ( COUNT ( Table[Store] ), FILTER ( ALLSELECTED ( Table ), Table[Approved] = TRUE() ), FILTER ( ALLSELECTED ( Table ), Table[Inventory] > 0 ) ) / CALCULATE ( COUNT ( Table[Store] ), FILTER ( ALLSELECTED ( Table ), Table[Approved] = TRUE() ) )

 

 

I have dummy data I can attach as well. Here is a snapshot.

 

DateStoreItemInventoryApprovedItem Level 1Item Level 2Item Level 3Item Level 4
9/16/20185088brussels sprouts10II4I453I453VV
9/16/20182839cabbage140SS2S200S200BW
9/16/20183475celery80TT2T2X3T2X3YC
9/20/20184323eggplant1690SS4S400S400BA
9/20/20187188leeks120II9I933I933MI
9/20/20184516lettuce60II1I108I108F1
9/20/20182682mushrooms50II9I943I943MT
9/20/20184104radishes130SS2S219S219BN
9/20/20183454squash161SS6S601S601D2
9/20/20185600swiss chard50SS2S208S208C2
9/20/20183277turnips120II7I753I753LG
9/20/20183764zucchini60II9I933I933MI

 

 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors