topic Re: DAX measure in DAX Commands and Tips

DAX measure

Kella — Wed, 09 Aug 2023 12:04:40 GMT

Hi,

How to I create a measure with Week Index for requirement below? Thanks ya'll in advance!

Example; Table Apple;

Product Group	Week1	Week2	Week3
AA	2	1	6
BB	3	3	7
CC	4	2	8
DD	5	5	9

If select week slicer (calendar table)=1
The calculation will be every week divided by 1st week;
> 2/2*100 =100 for Product group AA
> 1/2*100=50 for Product group AA for week 2
Divided depend on choosing week from week slicer.
Result;

Product Group	Week1	Week2	Week3
AA	100	50	300
BB	100	100	233
CC	100	50	200
DD	100	100	180

Re: DAX measure

collinq — Wed, 09 Aug 2023 16:18:44 GMT

Hi Kella ,

I think that you will get a better response in the DAX section so I am moving this request to that location.

Re: DAX measure

Anonymous — Fri, 11 Aug 2023 02:30:16 GMT

Hi Kella ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create measures.

Measure = RANKX ( FILTER ( ALL ( 'Table' ), 'Table'[Product Group] = SELECTEDVALUE ( 'Table'[Product Group] ) ), CALCULATE ( MAX ( 'Table'[week] ) ), , ASC )

Measure2 = VAR _1 = SELECTEDVALUE ( 'week slicer'[week ] ) VAR _2 = RANKX ( FILTER ( ALL ( 'Table' ), 'Table'[Product Group] = SELECTEDVALUE ( 'Table'[Product Group] ) ), CALCULATE ( [Measure] ), , ASC ) VAR _rankxpro = RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[Product Group] ) ), , ASC, DENSE ) VAR _value = IF ( _1 = [Measure] && _rankxpro = 1, MAX ( 'Table'[Value] ), BLANK () ) RETURN _value

Measure3 = VAR _rankxpro = RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[Product Group] ) ), , ASC, DENSE ) VAR _1 = MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Product Group] = SELECTEDVALUE ( 'Table'[Product Group] ) ), [Measure2] ) VAR _all = MAXX ( ALL ( 'Table' ), [Measure2] ) VAR _3 = IF ( _1 = BLANK (), _rankxpro + _all - 1, _1 ) RETURN ( MAX ( 'Table'[Value] ) / _3 ) * 100

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.