Hi All,
I have this expression in a microsoft power bi workbook:
=SUM(NUMERATOR_TABLE[NUMERATOR_AMOUNT])
/
SUMX( RELATEDTABLE(DENOMINATOR_TABLE), DENOMINATOR_TABLE[DENOMINATOR_AMOUNT])
I'm looking for help translating this into a qlik expression.
Basically it's a calculated measure where filters from the numerator table apply only to the numerator of the ratio. Filters in the denominator table's attributes apply to both the numerator and denominator.
The setup is that I have two tables.
One containing people with attributes like state, county, etc.
And one containing expenses generated by those people, with each person able to have none or many different expenses.
The tables are related on the person ID.
The expression is intended to calculate expense totals per person.
It lets me me filter different person categories and different expense categories while always having the denominator of the expression reflect the people meeting the filter selected from attributes in the person table.
The numerator should then be all expenses for the filtered people where the expenses meet the criteria for any filters selected from attributes in the expenses table.
For example, I could filter on the person table attribute state to "Texas" the ratio would be total expenses per person for persons residing in Texas.
If I then filter to "Housing" from the expense category attribute in the expenses table, I would see housing expenses per person, for all people residing in Texas. The denominator here would include counting people who have NO housing expenses.
If I just used sums and didn't have the the sumx and "related table" construct from power BI, these two filters would leave me showing sum of housing expenses for people in TX divided by the count of people residing in TX who had housing expenses instead of dividing by the count of all people in TX with or without housing expenses.
