Dynamic Measure

[Anonymous]

Hi, 

I'm creating a point in time report which requires a number of Dynamic components. I have the majority of them in the report but I seem to have fallen at the last hurdel. In the Screen shot below, I would like the last column 'TEST 0-30 days' measure to;

Sum "Balance" if "Age Category" = "0-30 days".

I'll break down each step below.

1. Balance = a field from the data sourceDate_Selected 
2. Date_Selected = CALCULATE(MAX('Date_Table'[Date]),ALLSELECTED('Date_Table'))
3. Doc_Age = ([Date_Selected] - MAX([Document.c2g__DueDate__c])) * 1
4. Age Category =
   IF([Doc_Age] < 1 ,"Current",
   IF([Doc_Age] < 30, "0-30 days",
   IF([Doc_Age] < 60, "31-60 days",
   IF([Doc_Age] < 90, "61-90 days",
   IF([Doc_Age] > 90, "90+ days", "Check")
   ))))​
5. TEST 0-30 days = SUMX('Transaction Line Item',
   if ('Key Measures'[Age Category]="0-30 days",
   CALCULATE(SUM('Transaction Line Item'[Balance])),
   BLANK()))

So Point number 5 doesn't seem to work, I also tired the point below and this threw an error.

1. Test 0-30 days = CALCULATE(SUM(Transaction Line Item'[Balance]),'Key Measures'[Age Category]="0-30 days")

From a model point of view the only tables relevent to these calculations are the DATE table and Transaction line item. these are linked by Date_Table[Date] to 'Transaction Line Item'[CreatedDate] on a 1:* relationship.

As always any help is greatly aprriciated. 

Thanks 

Dobby Libr3

[Greg_Deckler · Accepted solution]

SUMX(FILTER('Transaction Line Item','Key Measures'[Age Category]="0-30 days"),[Balance])

?

[amitchandak]

Row context is very important for measure

TEST 0-30 days = SUMX(summarize('Transaction Line Item','Transaction Line Item'[DocId],"_sum"
if ('Key Measures'[Age Category]="0-30 days",
CALCULATE(SUM('Transaction Line Item'[Balance])),
BLANK()))[_sum]

)

Also, if possible filter on age in the formula, not on the age category

[Anonymous]

Hi @amitchandak 

Thanks for getting back to me, I used your code and I'm still getting blanks!

[Greg_Deckler]

@Anonymous I don't see Key Measures table in your model, what is that and how is it connected? Is Age Category a column or a measure? Is TEST 0-30 days supposed to be a measure or a calculated column? If calculated column what table?

TEST 0-30 days = SUMX('Transaction Line Item',
if ('Key Measures'[Age Category]="0-30 days",
CALCULATE(SUM('Transaction Line Item'[Balance])),
BLANK()))

[Anonymous]

Hi @Greg_Deckler,

The Key Measure table is just a table where I keep all my measures, its stand alone and doesn't connect to the model. 

Age Category is currently a measure because as a colum it doesn't return the correct value. 

In an ideal world I would have these all as columns rather than measures, 

Thanks

Rob 

[Greg_Deckler]

Or perhaps:

SUMX(
  FILTER(
    ADDCOLUMS(
      'Transaction Line Item',
      "__Age Category",'Key Measures'[Age Category]
    ),
    [__Age Category]="0-30 days"
  ),
  [Balance]
)

[Anonymous]

Hi @Greg_Deckler , @ImkeF and @amitchandak, 

Unfortuantly none of those worked! thanks you so much for investing your time in this, I have got a sample PBIX below, is there somthing wrong with my data model maybe? 

https://drive.google.com/file/d/1o694rGjYHVia0xpwzq75Dl920fLK4_qC/view?usp=sharing

As always any help is appriciated and never expected!

Thanks 

Dobby 

[ImkeF · Accepted solution]

Hi @Anonymous ,

for these kind of measures, deleting the connection between your date table and the fact table should help.

[Anonymous]

Thanks @ImkeF this has worked, I really don't understand why! thanks all for your help

[Greg_Deckler · Accepted solution]

SUMX(FILTER('Transaction Line Item','Key Measures'[Age Category]="0-30 days"),[Balance])

?

[ImkeF]

Hi @Anonymous 

please try the following:

CALCULATE(

SUMX(
    VALUES('Transaction Line Item'[Trans Line Item]),
    CALCULATE(SUM('Transaction Line Item'[Balance]))),
    'Key Measures'[Age Category]="0-30 days")

[Anonymous]

Hi @ImkeF 

I tried a Calculate column before and I got this error. 

"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

This same error appeared when I tired this expression.

[ImkeF]

Hi @Anonymous 

have you tried @amitchandak  's proposal?

Otherwise: Could you please share a sample workbook?