Forum Discussion
Divide arbitrary two rows in a column
- 2 years ago
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.
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.