Calculate the sum of a value where a specific column has a lower value (DAX)

• Steven.Grzybowski

  Hall of Fame

  Points: 3214

  More actions

  May 6, 2019 at 4:02 pm

  #3636526

  I have an SSAS tabular Model with a table containing ledger data and another table containing account information.

  On this account filtering table I have a field called Typeorder.

  The Typeorders are as follows

  COA_Type ,Typeorder

  Revenue ,1

  COGS ,2

  Operating Expense ,3

  NonOperating Expense, 4

  Income Tax ,5

  Asset ,6

  Liability ,7

  Equity, 8

  I have a measure called [Delta (Reversed)] that is used for the change in the account value (The original accounting system shows revenue as a negative number rather than positive) .  What I would  like to do is to calculate a margin of the sum of every record with a type order lower than the current rows typeorder.

  I would want to calculate the [Delta (Reversed)]  where typeorder <= the current account types  typeorder.

  So for cost of good sold, it would be the [Delta (Reversed)] where typeorder <= 2

  For Operating Expense it would be [Delta (Reversed)] where typeorder <= 3

  The idea would be to lay it out in a pivottable with row grouping on COA_Type, with values of Delta(reversed) and margin.

  How would I go about doing something like this?

  Right now, The formula I have is

  Margin:= CALCULATE([Delta (Reversed)]) +

  CALCULATE([REVENUE Delta (Reversed)],ALL('_Account Filtering'[COA_TYPE],'_Account Filtering'[COA_Group]),FILTER(ALL('_Account Filtering'[TypeOrder]),'_Account Filtering'[TypeOrder] <= '_Account Filtering'[TypeOrder] ))

  Which is giving me the expected value for margin for COGS, but is not yielding the expected amount for Operating Expense, and appears to be showing me only Operating Expense + Revenue, not operating Expense + COGS +Revenue.

   

  • This topic was modified 5 years, 6 months ago by  Steven.Grzybowski.