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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Anonymous
Not applicable

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 .

Anonymous
Not applicable

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.

Anonymous
Not applicable

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors