Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Measures at a certain granularity

Hey All,

 

I have a power  BI report with dataset (week, business unit, SKU, Inbound, Outbound). I am creating a measure "Subtract" which is Inbounds- outbounds. And then I am creating another measure "Non Negative MEAS1" which basically zero's out whenever inbound> outbound else it returns "Inbound- Outbounds". I am creating this measure at SKU grain because I want the "Overall" to be a sum of individual "Non-Negative MEAs1" at SKU grain.  I have below code so far but somehow the total doesn't sum up each week's data. Instead it's just adding data for the first week. 

 

NonNegMeas1 = CALCULATE(SUMX(
VALUES('Table'[SKU] ),
calculate((CALCULATE( if('Table'[Subtract]<0,0,'Table'[Subtract]),
ALL('Table'[SKU]),
SUMMARIZE('Table', 'Table'[SKU])
)))))
 
 
 
However, when I have Inbounds- Outbounds as a calculated column, NonNegMeas seems to work fine. Is there a way to make it work when I have "Inbounds-Outbounds" as a measure? in my actual dataset, it's not possible to have it as a column. I need it as a measure. PBI link
 
Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    It sounds like nest aggregation calculated requirement, you can add a variable table to your measure formula to manually summary the original table and add temp columns to store measure result 'inbound' and 'outbound'.

    After these steps, you can use iteration functions to the summary above variable table result.

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Could you please show me in the power bi attached? I am very new here and don't really know who will it work

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

        You can try to use the following measure formula to manually summary with measure inbound, outbound and calculate out diff:

         

        NonNegMeas =
SUMX (
    SUMMARIZE (
        'Table',
        [Week],
        [BU],
        'Table'[SKU],
        "Diff", SUM ( 'Table'[Inbounds] ) - SUM ( 'Table'[Outbounds] )
    ),
    IF ( [Diff] < 0, 0, [Diff] )
)

         

        Notice: replace the bold part with your measure formula 'inbound', 'outbound'.

        If above not helps, can you please share pbix file with dummy data and calculate column formulas for test? (I'm not so clear your original table data structure and Dax formulas)

        Regards,

        Xiaoxin Sheng