Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
prakash_satya59
Frequent Visitor

Need Help on Dax - % Change

 

Dear Team

Good Morning / Good Evening / Good Afternoon.

 

I have asked question like this before however this time with new requirement.

 

I need % change for dynamic column change value.

(As per given image)

I am taking blue line column and orange line column through field Parameter and that is measure (4 different measure)
Some time Q1 or Q2 or Q3 or Q4.

 

I want to get % change of selected column from Parameter slicer.
It could be Q1 - Q4 or Q2-Q3 or Q3-Q4 or Q1 - Q3..etc.

 

Requesting to you..Please assist me for this.

 

Need help.pngPlease assist.

 

Thanks

Satya Prakash Pandey

 

1 ACCEPTED SOLUTION

Hi @prakash_satya59 ,

Just need adjust the dax formula simply, please try below dax formual:

Measure =
VAR _str =
    CONCATENATEX ( SELECTCOLUMNS ( Parameter, "Test", [Parameter] ), [Test] )
VAR cur_own =
    SELECTEDVALUE ( 'Table 1'[Owning Practice] )
VAR _a =
    CALCULATE ( MAX ( 'Table 1'[Q1] ), 'Table 1'[Owning Practice] = cur_own )
VAR _b =
    CALCULATE ( MAX ( 'Table 1'[Q2] ), 'Table 1'[Owning Practice] = cur_own )
VAR _c =
    CALCULATE ( MAX ( 'Table 1'[Q3] ), 'Table 1'[Owning Practice] = cur_own )
VAR _d =
    CALCULATE ( MAX ( 'Table 1'[Q4] ), 'Table 1'[Owning Practice] = cur_own )
VAR _e =
    SUMX ( ALL ( 'Table 1' ), [Q1] )
VAR _f =
    SUMX ( ALL ( 'Table 1' ), [Q2] )
VAR _g =
    SUMX ( ALL ( 'Table 1' ), [Q3] )
VAR _h =
    SUMX ( ALL ( 'Table 1' ), [Q4] )
VAR s1 = "Q1Q2"
VAR s2 = "Q1Q3"
VAR s3 = "Q1Q4"
VAR s4 = "Q2Q3"
VAR s5 = "Q2Q4"
VAR s6 = "Q3Q4"
VAR _val =
    SWITCH (
        _str,
        s1,
            ( _b - _a ) / _a,
        s2,
            ( _c - _a ) / _a,
        s3,
            ( _d - _a ) / _a,
        s4,
            ( _c - _b ) / _b,
        s5,
            ( _d - _c ) / _b,
        s6,
            ( _d - _c ) / _c
    )
VAR _val2 =
    SWITCH (
        _str,
        s1,
            ( _f - _e ) / _e,
        s2,
            ( _g - _e ) / _e,
        s3,
            ( _h - _e ) / _e,
        s4,
            ( _g - _f ) / _f,
        s5,
            ( _h - _f ) / _f,
        s6,
            ( _h - _g ) / _g
    )
RETURN
    IF ( HASONEVALUE ( 'Table 1'[Owning Practice] ), _val, _val2 )

vbinbinyumsft_0-1670811683676.png

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
prakash_satya59
Frequent Visitor

Thanks for the help. It really worked for me.

However can you help for total of the column and total % change of the % change column as its not coming .

Hi @prakash_satya59 ,

Just need adjust the dax formula simply, please try below dax formual:

Measure =
VAR _str =
    CONCATENATEX ( SELECTCOLUMNS ( Parameter, "Test", [Parameter] ), [Test] )
VAR cur_own =
    SELECTEDVALUE ( 'Table 1'[Owning Practice] )
VAR _a =
    CALCULATE ( MAX ( 'Table 1'[Q1] ), 'Table 1'[Owning Practice] = cur_own )
VAR _b =
    CALCULATE ( MAX ( 'Table 1'[Q2] ), 'Table 1'[Owning Practice] = cur_own )
VAR _c =
    CALCULATE ( MAX ( 'Table 1'[Q3] ), 'Table 1'[Owning Practice] = cur_own )
VAR _d =
    CALCULATE ( MAX ( 'Table 1'[Q4] ), 'Table 1'[Owning Practice] = cur_own )
VAR _e =
    SUMX ( ALL ( 'Table 1' ), [Q1] )
VAR _f =
    SUMX ( ALL ( 'Table 1' ), [Q2] )
VAR _g =
    SUMX ( ALL ( 'Table 1' ), [Q3] )
VAR _h =
    SUMX ( ALL ( 'Table 1' ), [Q4] )
VAR s1 = "Q1Q2"
VAR s2 = "Q1Q3"
VAR s3 = "Q1Q4"
VAR s4 = "Q2Q3"
VAR s5 = "Q2Q4"
VAR s6 = "Q3Q4"
VAR _val =
    SWITCH (
        _str,
        s1,
            ( _b - _a ) / _a,
        s2,
            ( _c - _a ) / _a,
        s3,
            ( _d - _a ) / _a,
        s4,
            ( _c - _b ) / _b,
        s5,
            ( _d - _c ) / _b,
        s6,
            ( _d - _c ) / _c
    )
VAR _val2 =
    SWITCH (
        _str,
        s1,
            ( _f - _e ) / _e,
        s2,
            ( _g - _e ) / _e,
        s3,
            ( _h - _e ) / _e,
        s4,
            ( _g - _f ) / _f,
        s5,
            ( _h - _f ) / _f,
        s6,
            ( _h - _g ) / _g
    )
RETURN
    IF ( HASONEVALUE ( 'Table 1'[Owning Practice] ), _val, _val2 )

vbinbinyumsft_0-1670811683676.png

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Sir
Thnks for the support..However may you help to get total of below 
Q1 or Q2 or Q3 or Q4

prakash_satya59_0-1672415622753.png

 



Hi Sir..

Please help for this info.

All is worked however I want to total of first two columns..which is not coming currently.

v-binbinyu-msft
Community Support
Community Support

Hi @prakash_satya59 ,

Please try below steps:

1. because you don't provide sample data, i create a test table for test according to your descriptions

Table:

vbinbinyumsft_0-1670482741643.png

2. create a measure with below dax formula

% Change =
VAR _str =
    CONCATENATEX ( SELECTCOLUMNS ( Parameter, "Test", [Parameter] ), [Test] )
VAR cur_own =
    SELECTEDVALUE ( 'Table 1'[Owning Practice] )
VAR _a =
    CALCULATE ( MAX ( 'Table 1'[Q1] ), 'Table 1'[Owning Practice] = cur_own )
VAR _b =
    CALCULATE ( MAX ( 'Table 1'[Q2] ), 'Table 1'[Owning Practice] = cur_own )
VAR _c =
    CALCULATE ( MAX ( 'Table 1'[Q3] ), 'Table 1'[Owning Practice] = cur_own )
VAR _d =
    CALCULATE ( MAX ( 'Table 1'[Q4] ), 'Table 1'[Owning Practice] = cur_own )
VAR s1 = "Q1Q2"
VAR s2 = "Q1Q3"
VAR s3 = "Q1Q4"
VAR s4 = "Q2Q3"
VAR s5 = "Q2Q4"
VAR s6 = "Q3Q4"
RETURN
    SWITCH (
        _str,
        s1,
            ( _b - _a ) / _a,
        s2,
            ( _c - _a ) / _a,
        s3,
            ( _d - _a ) / _a,
        s4,
            ( _c - _b ) / _b,
        s5,
            ( _d - _c ) / _b,
        s6,
            ( _d - _c ) / _c
    )

3. add a table visual with fields and measure

Animation22.gif

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors