DAX - How to use a Column Value to use in a Measure

gritman — Sun, 19 May 2019 23:10:52 GMT

Hello everyone,

I would very much appreciate any help and advice on the following please.

I have a table and would like to perform some calculations, the inputs to which will change, depending on slicer values. I think I therefore need to use Measures instead of Columns (?).

Below is an example: the first 3 columns are imported data. The measure is a sum of the $ Amount per Sales Person - which I performed using CALCULATE(SUM(Sales[$ Amount]), ALLEXCEPT(Sales, Sales[Salesperson]))

What I now want to do is $ Amount / Total (e.g. for row 1: 200 / 1000) however I can't seem to do this in DAX. I've been reading and I understand that is because I can't pass a table as a parameter (I tried (Divide(Table[$Amount], Measure.Total), 0) but that didn't work).

Please can someone advise on a measure that will perform Table.$ Amount / Measure.Total for each row.

Table.Date	Table.Salesperson	Table.$ Amount	Measure.Total	Measure.Day % (Help)
1st Feb	Bob	200	1000	20%
1st Feb	Jessica	200	1200	17%
2nd Feb	Bob	300	1000	30%
2nd Feb	Jessica	400	1200	33%
3rd Feb	Bob	500	1000	50%
3rd Feb	Jessica	600	1200	50%

The reason I want a measure rather then a Column is so I can have a slider on the date so that if I was to change the date range to just 1st Feb and 2nd Feb as below, the Day % would change.

Table.Date	Table.Salesperson	Table.$ Amount	Measure.Total	Measure.Day % (Help)
1st Feb	Bob	200	500	40%
1st Feb	Jessica	200	600	33%
2nd Feb	Bob	300	1000	30%
2nd Feb	Jessica	400	1200	33%

Any help would be greatly appreciated.

Thanks

Gritman

Re: DAX - How to use a Column Value to use in a Measure

parry2k — Mon, 20 May 2019 00:59:29 GMT

gritman add following measures, Total Sales and % Sales

Total Sales = 
VAR __salesPerson = MAX( Table[Salesperson] )
RETURN
CALCULATE(SUM(Table[$ Amount]), 
ALLSELECTED( Table ), 
Table[Table.Salesperson] = __salesPerson )

% Sales = DIVIDE( SUM( Table[$ Amount] ), [Total Measure] )

Re: DAX - How to use a Column Value to use in a Measure

gritman — Mon, 20 May 2019 03:26:51 GMT

Hi Parry2k,

That worked! Many, many thanks.

Rgds

Gritman

topic DAX - How to use a Column Value to use in a Measure in DAX Commands and Tips

DAX - How to use a Column Value to use in a Measure

Re: DAX - How to use a Column Value to use in a Measure

Re: DAX - How to use a Column Value to use in a Measure