Forum Discussion

Kella's avatar
Kella
Frequent Visitor
3 years ago
Solved

DAX measure

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



 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

     

     

     

2 Replies

  • collinq's avatar
    collinq
    Icon for Super User rankSuper User
    3 years ago

    Hi Kella ,

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.