Divide arbitrary two rows in a column

[Anonymous]

Hello, I have a data set that looks like the following, and I want to divide the quantities of any two products that I determine based on a filter (i.e., no hardcoding Product into code). Is there a way I can do it without duplicating tables?

Product	Quantity
a	1
b	2
c	2
d	3
e	1
f	2
g	2

[technolog · Accepted solution]

You want to divide the quantities of two products that you select, without hardcoding the product names into the DAX formula and without duplicating tables. Here's a way to do it:

First, you'll need two slicers on your report, one for the numerator and one for the denominator. These slicers will allow you to select which product's quantity you want to use as the numerator and which one as the denominator.

To make this work, you can create two separate measures that capture the selected value from each slicer. Let's call these measures SelectedNumerator and SelectedDenominator.

For SelectedNumerator, the DAX might look something like:

SelectedNumerator =
IF(
HASONEVALUE('YourTableName'[Product]),
SUMX(FILTER('YourTableName', 'YourTableName'[Product] = VALUES('YourTableName'[Product])), 'YourTableName'[Quantity]),
BLANK()
)
And similarly for SelectedDenominator:

SelectedDenominator =
IF(
HASONEVALUE('YourTableName'[Product]),
SUMX(FILTER('YourTableName', 'YourTableName'[Product] = VALUES('YourTableName'[Product])), 'YourTableName'[Quantity]),
BLANK()
)
Now, you can create a third measure to divide these two:

DivisionResult =
IF(
NOT(ISBLANK([SelectedNumerator])) && NOT(ISBLANK([SelectedDenominator])) && [SelectedDenominator] <> 0,
[SelectedNumerator] / [SelectedDenominator],
BLANK()
)
Place the DivisionResult measure on your report, and use the two slicers to select the products for the numerator and denominator. The result will show the division of the selected products' quantities.

Regarding your last question about labeling the two with the name of the specific rows instead of "numerator" and "denominator", you can simply rename the slicer titles to the product names you select. But remember, the slicers will still show all product names as options, so it's up to the user to remember which slicer is for the numerator and which one is for the denominator.

[Anonymous]

Duplicating... which tables? If you want to be able to say which selected product is the numerator and which is the denominator... then you have to have 2 tables that will act as 2 independent slicers of products.

Best
D

[Anonymous]

Is there a way for me to label the two with the name of the specific rows instead of "numerator" and "denominator"?