Applying specific criteria to a custom calculation
Is there a way to apply specific criteria/filters to a custom calculation?
I have a measure that is essentially just a distinct count. I then want a calculation to take the measure apply some criteria and divide it with the same measure but with different criteria.
For example:
Count of people where shirt color is red divided by count of people where shirt color is blue.
I can make this change in the cube and do it with DAX, but is it possible to do it with a custom calculation in the front end?
DAX would look something like
DIVIDE(
CALCULATE(
DISTINCTCOUNT('Dim_People'[PersonID]),
'Dim_People'[ShirtColor] = "Red"
),
CALCULATE(
DISTINCTCOUNT('Dim_People'[PersonID]),
'Dim_People'[ShirtColor] = "Blue"
)
)
The idea is really to have two KPI objects: One with count of people with red shirts and then a second KPI object where it's count of people with red shirts divided by count of people with blue shirts. Maybe you are able to refer to measures in separate objects somehow to obtain this?
Thanks!
Comments
Hi Malte,
In the TARGIT client, I think you can achieve what you are asking for by applying a User Dimension with two members.
One member with the criteria ShirtColor = Red, and the other with criteria ShirtColor = Blue.
Create a crosstab with PersonIDs on the vertical axis and with the DistinctCount measure - then add the User Dimension on the horizontal axis, so it becomes two columns.
You can then easily achieve the desired calculated column as sum(d-2, 0, m1) / sum(d-1, 0, m1).
BR / Ole
Please sign in to leave a comment.