Forum Discussion

NBOnecall's avatar
NBOnecall
Icon for Helper V rankHelper V
7 years ago

Help trying to sum a measure

Hi,

 

I have the following equation for a measure -

 

PosLossSalesAll = 

CALCULATE(sum('ns InventorySnapshot Group by SKU'[ThirdOptionFirstDate]))
* 
DIVIDE(sum('Demand 2 1 18 - 6 23 19'[Quantity]),CALCULATE(COUNTROWS('ns InventorySnapshot Group by SKU'),FILTER('ns InventorySnapshot Group by SKU','ns InventorySnapshot Group by SKU'[OnHand]<>0)))
*
(var itemInternalIDAmount = SELECTEDVALUE('ns InventorySnapshot Group by SKU'[ItemInternalId])
Return
calculate(AVERAGEx('Demand 2 1 18 - 6 23 19','Demand 2 1 18 - 6 23 19'[Amount]),
FILTER( ALL('Demand 2 1 18 - 6 23 19'[Internal ID_1]), 'Demand 2 1 18 - 6 23 19'[Internal ID_1] = itemInternalIDAmount))
/
var itemInternalIDQTY = SELECTEDVALUE('ns InventorySnapshot Group by SKU'[ItemInternalId])
Return
calculate(AVERAGEx('Demand 2 1 18 - 6 23 19','Demand 2 1 18 - 6 23 19'[Quantity]),
FILTER( ALL('Demand 2 1 18 - 6 23 19'[Internal ID_1]), 'Demand 2 1 18 - 6 23 19'[Internal ID_1] = itemInternalIDQTY)))

It is saying that Possible lost sales = Days Out of Stock * Demand Per Day in Stock * AvgSale

 

I am getting the correct result per row, but it doesn't sum at the total line of the table visulization.

 

Any help would be apperciated. I have searched on this forum for a bit now and can't find a use case that helps me.

 

Thanks,

Noel

Need Help

9 Replies

  • Cmcmahan's avatar
    Cmcmahan
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Ahhh, yes.  Trying to fight the default sums in a table/matrix.  You can read about my adventures fighting this particular feature of Power BI here.  Long story short, PowerBI doesn't sum measures in the total section of a table.  It re-calculates the measure, but with the context of the entire section instead of the individual row.

     

    So because your measure is so complicated, I'm not even going to attempt to re-write it.  The problem is that you're using SELECTEDVALUE and when totalling, there are multiple values so you get a blank instead of a sum. You may be able to simplify your original measure to not specify the filter context, instead letting the table set up the context for you.  I'm not sure how your rows are grouped, but you essentially have to do something like:

    PossibleLostSalesWithTotal = IF(ISFILTERED(<Column you group rows by>), [PosLossSalesAll], <Calculate entire total here>)

    It's not pretty, and will likely duplicate a lot of your measure, but without being more familiar with your data, that's the best I've got.

    • NBOnecall's avatar
      NBOnecall
      Icon for Helper V rankHelper V
      7 years ago

      I have two tables, one for Demand and one for the Inventory Levels on a ceratin day. The Demand table show number of sales per day for the specific item (ItemInternalID). It is a lot of calculated columns and measures to get me to my Possible lost sales = Days Out of Stock * Demand Per Day in Stock * AvgSale as you can see, but even getting to that point was difficult as well. I guess I don't understand the

      <Calculate entire total here>

      part of your equation. I am guessing that it is the total Possible Lost Sales somehow calculated without me using my above equation? But I don't see a way of getting there without using my equation.

       

      Thanks,

      Noel

      • Cmcmahan's avatar
        Cmcmahan
        Icon for Resident Rockstar rankResident Rockstar
        7 years ago

        You can use the above equation as a starting point, but can't just copy/paste it.

        PowerBI is re-evaulating that measure for the total, but in a context where there are multiple ItemInteralId's.  Your measure uses SELECTEDVALUE to get the current [ItemInternalId], but returns a blank when there are multiple IDs available like in the total row.

         

        I haven't really messed with your data, so I don't know how close this will be to working, but you might try editing your original measure like this:

        PosLossSalesAll2 = 

SUM('ns InventorySnapshot Group by SKU'[ThirdOptionFirstDate])
* 
DIVIDE(sum('Demand 2 1 18 - 6 23 19'[Quantity]),CALCULATE(COUNTROWS('ns InventorySnapshot Group by SKU'),FILTER('ns InventorySnapshot Group by SKU','ns InventorySnapshot Group by SKU'[OnHand]<>0)))
*
AVERAGE('Demand 2 1 18 - 6 23 19'[Amount])
/
AVERAGE('Demand 2 1 18 - 6 23 19'[Quantity])
)

        Before, you were going through and explicitly defining filters and context, when the table does that for you.  I just removed anything that filtered based on ItemInternalId because the table visualization is doing that for you.  This has the added benefit of being much easier to read and edit.