Forum Discussion

SS12's avatar
SS12
New Member
6 years ago

Divide two measures when Date Matches

Hi,

 

I am using an excel file that has the following columns activation date, feature ID, Tenant Ids(not shown in the below picture). I have calculated the Running total per category (Parent, Child1, Child 2) using DAX Formula: 

 
Running Total Tenant ID =
CALCULATE(
    DISTINCTCOUNT('Table'[tenant_id]),
    FILTER(ALLSELECTED('Table'),'Table'[activation_quarter]<=MAX('Table'[activation_quarter]) && ' Table'[feature_id]=MAX('Table'[feature_id])
        )
    )
 
Now, I want to calculate the percentage of all the CHILD IDs w.r.t to PARENT ID running total (instead of grand total). For example, At Q1 2019->CHILD1 % = 5/10 (as shown in comment section). I tried the following DAX formula, but getting an error (The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.)
 
Percent = DIVIDE('Table'[Running Total Tenant ID], FILTER('Table','Table'[feature_id]="Parent"),0)
 
I don't know how to match the activation quarter for the running totals of CHILD ID and Parent ID. Please, help!!
 

1 Reply

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago
    Current = 
var _currentquarter = table[activation quarter]
//do your calcs where the rows have Feature Id = Parent, and table[activation quarter] = _currentquarter.
    var _calc = CALCULATE(MAX(table[Running Total]), table [Feature ID] = "Parent", table[activation quarter] = _currentquarter)
return _calc

    Hi SS12 ,

    Don't know if you have used var yet, but very useful in situations like this.  You will always have to have a return.  Var stands for variable.


    So, in this situation you are on the row with the child, the var grabs that rows activation quarter.  Now we can use it to filter the table where the Feature ID = Parent, so that brings it down to 3 rows, and then where activation quarter = _currentquarter, which brings it down to 1 row in your example, then return the value of the running total. 

    As I don't have your pbix, can't swear to the measure, but this should help.
    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel