MedianX equivalent

[MT6]

Hi,

I am trying to recreate certain measure from Dax language to qlik.

The goal is to create measure that firstly calculates an average value weighted by given dimension and then returns median. Measure then should be usable in sheet table by different dimensions. In power bi this is resolved by combining MedianX function with summarize. Please see basic example with dummy data below:

1. Power BI

Company Name || Product Name || Price

A	x	1200
A	x	5000
A	y	4200
A	y	3400
B	y	5000
B	x	1500
B	g	7200
B	g	2300

2. QLIK Sense

Same dataset,

I ve tried below function:

MEDIAN(Aggr(AVG([Price]),[Company Name]))

But the results are different:

As you can see Total values are calculated correctly.

Any ideas how to solve this?

Note: In above example I used 'Product Name' as a dimension in sheet table however in my real data i am trying to create this weighted measure to be usable by different dimensions. 

[Or]

I don't use DAX, but at first glance it seems like your issue might be with a missing AGGR field:

MEDIAN(Aggr(AVG([Price]),[Company Name],[Product Name]))

You want your data aggregated by a combination of product and company, not just company.

[MT6]

Thanks for a hint, it does the job for a hard coded measure. However is it possible to make this second aggregation parameter (in this case 'Product Name') dynamic?

For example, I have got multiple columns like 'Product Name', I am wondering if it is possible to create a one measure that can be used in multiple sheet tables when second aggregation parameter will depend on dimension used in the table.

[Or]

I don't know if there's any way to make the aggr itself dynamic as a master item, as the structured parameter needs to be a field rather than an expression. You could perhaps try a master item along the lines of:

Pick(Match(GetObjectDimension(),'Product Name','AnotherField'),

MEDIAN(Aggr(AVG([Price]),[Company Name],[Product Name]))

MEDIAN(Aggr(AVG([Price]),[Company Name],[AnotherField])))

Edit:

Looks like it'll take dollar sign expansion, so perhaps:

Median(Aggr(AVG([Price]),[Company Name], [$(=GetObjectField())]))

[MT6 · Accepted solution]

Many thanks for your help.

Seems like in my case below expression does the job, i need to test this more but it might be useful for others.

Median(Aggr(AVG([Price]),[Company Name], $(='[' & GetObjectDimension() & ']')))