DAX measure

[Kella]

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

[Anonymous · Accepted solution]

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.

[collinq]

Hi @Kella ,

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