topic Re: How to create measure that selects what value to use as the denominator in Divide. in DAX Commands and Tips

How to create measure that selects what value to use as the denominator in Divide.

moltra — Mon, 23 Nov 2020 19:44:47 GMT

I fixed it in one of the reference measures. Never did figure out how to select a variable to use in a expression like divide.

I am trying to create a measure where the denominator changes based on the shared axis value.

I need to change the Denominator depending on what is being Calculated.

I have tried IF statements, but will not allow me to use the Table column or measures that show the value to be used as the Logic test.

I know the below code is not all correct, but it should get the question across.

i.e.

TotalSalesRatio = Var Totalsalespeople = [AllSalesPeople] Var TotalsalesoneSalesperson = [SalesPerson] Var DesiredReportSales = "" If([DesiredReport]="all", AllSalesPeople, TotalsalesoneSalespPerson) Divide([TotalSales],DesiredReportSales,0)

Re: How to create measure that selects what value to use as the denominator in Divide.

Pragati11 — Mon, 23 Nov 2020 16:10:04 GMT

Hi moltra ,

Your query is not making any sense. The only thing I understand here is you want to use DIVIDE function in DAX but you are not able to use it.

Add some sample data and expected output to show what your actual requirement is.

Thanks,

Pragati

Re: How to create measure that selects what value to use as the denominator in Divide.

moltra — Mon, 23 Nov 2020 16:18:51 GMT

I have a Visual that needs to be different during the week than it is on weekends. During the week, the measure is based on a weekly peak of a subset of equipment. On the weekends it is the Peak of all the equipment.

I need to figure out how to calculate the Ratio for the total power used divided by both the "Weekly Peak of subset of equipment" and "Peak of all the equipment", one at a time.

This visual needs to have the ratio for total Power used divided by both the Subset and all calculated values.

Re: How to create measure that selects what value to use as the denominator in Divide.

moltra — Mon, 23 Nov 2020 16:39:27 GMT

What I need to be returned by the measure. I have highlighted the number that I need to be able to select via an if statement or someother command.

	Total Power(input)	Peak Power(Input)	Ratio (output)
Area1	5531.68	9812.91	56.37%
Area2	819.26	1211.00	68%

What I am getting returned by the measure

	Total Power(input)	Peak Power(input)	Ratio(output)
Area1	5531.68	9812.91	56.37%
Area2	819.26	3459.02	23.68%

Re: How to create measure that selects what value to use as the denominator in Divide.

moltra — Mon, 30 Nov 2020 16:13:35 GMT

I fixed it in one of the reference measures. Never did figure out how to select a variable to use in a expression like divide.

I added a filter in one of the reference measures so that the data sent to the divide calculation had the required information.