Forum Discussion

darylmc's avatar
darylmc
Frequent Visitor
6 years ago
Solved

Measure - same day last week per column

Hi All,

 

I'm looking to create a measure for availability for the same day last week. I've created the following formula which mostly works

 

LW Avail = calculate(Divide(Sum(History[avail_sum]),Sum(History[avail_count]),""),filter(all
(History),History[Date]=MAX(History[Date])-7),FILTER(All(History),History[Store]=MAX(History[Store])),FILTER(All(History),History[Product]=MAX(History[Product])))
 
However, as per the formula, the total per store is taking the number of one of the Products.
 
So it's giving this:
 
ShopProductAvail
AA24%
 B50%
 C76%
 Total24%
BA12%
 B20%
 C28%
 Total

12%

 

When I would expect something like this:

 

ShopProductAvail
AA24%
 B50%
 C76%
 Total50%
BA12%
 B20%
 C28%
 Total20%

 

Any help appreciated,

 

Thanks

Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    Hi, darylmc ,

     

    you could write your measure like this:

    LW Avail =
VAR _product =
    CALCULATE ( SELECTEDVALUE ( History[Product] ) )
VAR _date =
    CALCULATE ( MAX ( History[Date] ) )
VAR _store =
    CALCULATE ( SELECTEDVALUE ( History[Store] ) )
RETURN
    IF (
        HASONEVALUE ( History[Product] ),
        CALCULATE (
            DIVIDE ( SUM ( History[avail_sum] ), SUM ( History[avail_count] ), "" ),
            FILTER (
                ALL ( History ),
                History[Date] = _date - 7
                    && History[Store] = _store
                    && History[Product] = _product
            )
        ),
        CALCULATE (
            DIVIDE ( SUM ( History[avail_sum] ), SUM ( History[avail_count] ), "" ),
            FILTER ( ALL ( History ), History[Date] = _date - 7 && History[Store] = _store )
        )
    )

     

    Cheers,
    Sturla

3 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hi, darylmc ,

     

    you could write your measure like this:

    LW Avail =
VAR _product =
    CALCULATE ( SELECTEDVALUE ( History[Product] ) )
VAR _date =
    CALCULATE ( MAX ( History[Date] ) )
VAR _store =
    CALCULATE ( SELECTEDVALUE ( History[Store] ) )
RETURN
    IF (
        HASONEVALUE ( History[Product] ),
        CALCULATE (
            DIVIDE ( SUM ( History[avail_sum] ), SUM ( History[avail_count] ), "" ),
            FILTER (
                ALL ( History ),
                History[Date] = _date - 7
                    && History[Store] = _store
                    && History[Product] = _product
            )
        ),
        CALCULATE (
            DIVIDE ( SUM ( History[avail_sum] ), SUM ( History[avail_count] ), "" ),
            FILTER ( ALL ( History ), History[Date] = _date - 7 && History[Store] = _store )
        )
    )

     

    Cheers,
    Sturla

    • darylmc's avatar
      darylmc
      Frequent Visitor
      6 years ago

      Works like a charm!

       

      Thanks

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Try to create a new measure like

    averagex(summarize(table,table[shop],table[Product],"_avg",[LW Avail]),_avg)

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
    Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

    Connect on Linkedin