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
sarath_chandra
Helper III
Helper III

Looking for diff of two values by filters

Hi all, please help me for solution.

kindly see the data set . there is cycle column with Q1,Q2,Q3. with ID , Amount column

I need difference of two cycle (which i'm selecting in filter) higher cycle will be base in difference

i attached dax, power bi view images also. 

Dax which previously tried showing the difference(q3-q2-q1) which is working while im selecting q3 and q2 or q3 and q1.

but if im selecting q2 and q1 in filter . it should show q2 - q1 as answer. but this dax showing ( -q2-q1). here q2 should be base in difference.

please help me out ?

highly appreciate for solution please see the data set with cycle column q3,q2,q1please see the data set with cycle column q3,q2,q1dax which actually giving q3-q2-q1 . but i need , if im selecting q2 and  q1 in filter , it should show q2 - q1 . but it showing -q2-q1  .(higher cycle is base in difference)dax which actually giving q3-q2-q1 . but i need , if im selecting q2 and q1 in filter , it should show q2 - q1 . but it showing -q2-q1 .(higher cycle is base in difference)if im selecting q2 and q1 , q2 is higher order cycle ,so q2 is base in difference . so it should show q2-q1if im selecting q2 and q1 , q2 is higher order cycle ,so q2 is base in difference . so it should show q2-q1

2 ACCEPTED SOLUTIONS
ERD
Super User
Super User

Hello @sarath_chandra ,

Hope this measure helps:

result = 
VAR current_id = SELECTEDVALUE ( 'Table'[ID] )
VAR current_cycle = SELECTEDVALUE ( 'Table 2'[Cycle] )
VAR t =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table'[Cycle] ),
        "@maxrank",
            CALCULATE (
                MAX ( 'Table 2'[Rank] ),
                'Table 2'[Cycle] IN ALLSELECTED ( 'Table'[Cycle] ),
                REMOVEFILTERS ()
            ),
        "@minrank",
            CALCULATE (
                MIN ( 'Table 2'[Rank] ),
                'Table 2'[Cycle] IN ALLSELECTED ( 'Table'[Cycle] ),
                REMOVEFILTERS ()
            )
    )
VAR maxRank = MAXX ( t, [@maxrank] )
VAR minRank = MINX ( t, [@minrank] )
VAR maxCycle =
    CALCULATE (
        MAX ( 'Table 2'[Cycle] ),
        'Table 2'[Rank] = maxRank,
        REMOVEFILTERS ()
    )
VAR minCycle =
    CALCULATE (
        MAX ( 'Table 2'[Cycle] ),
        'Table 2'[Rank] = minRank,
        REMOVEFILTERS ()
    )
VAR _sAmountMaxCycle =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        'Table'[ID] = current_id,
        'Table'[Cycle] = maxCycle
    )
VAR _sAmountMinCycle =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        'Table'[ID] = current_id,
        'Table'[Cycle] = minCycle
    )
VAR result =
    SWITCH (
        SELECTEDVALUE ( 'Table 2'[Cycle] ),
        "XDifference", _sAmountMaxCycle - _sAmountMinCycle,
        IF (
            MAX ( 'Table 2'[Cycle] ) IN ALLSELECTED ( 'Table'[Cycle] ),
            CALCULATE (
                SUM ( 'Table'[Amount] ),
                'Table'[ID] = current_id,
                'Table'[Cycle] = current_cycle
            )
        )
    )
RETURN
    result

ERD_0-1669896402028.png

Please, take into account that I've added a Rank column to the Table 2:

ERD_1-1669896472202.png

 

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

v-jianboli-msft
Community Support
Community Support

Hi @sarath_chandra ,

 

Approve with @ERD .

Besides, I have modified my measure. Please try:

Measure = 
VAR _a =
    CALCULATE ( MAX ( 'Table'[Cycle] ), ALLEXCEPT ( 'Table', 'Table'[Cycle] ) )
VAR _b =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( ALL ( 'Table' ), [ID] = MAX ( 'Table'[ID] ) && [Cycle] = _a )
    )
VAR _c =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( 'Table' , [ID] = MAX ( 'Table'[ID] ) && [Cycle] < _a )
    )
RETURN _b-_c

Final output:

vjianbolimsft_1-1669963527789.png

vjianbolimsft_2-1669963542720.png

Best Regards,

Jianbo Li

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

16 REPLIES 16
v-jianboli-msft
Community Support
Community Support

Hi @sarath_chandra ,

 

Approve with @ERD .

Besides, I have modified my measure. Please try:

Measure = 
VAR _a =
    CALCULATE ( MAX ( 'Table'[Cycle] ), ALLEXCEPT ( 'Table', 'Table'[Cycle] ) )
VAR _b =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( ALL ( 'Table' ), [ID] = MAX ( 'Table'[ID] ) && [Cycle] = _a )
    )
VAR _c =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( 'Table' , [ID] = MAX ( 'Table'[ID] ) && [Cycle] < _a )
    )
RETURN _b-_c

Final output:

vjianbolimsft_1-1669963527789.png

vjianbolimsft_2-1669963542720.png

Best Regards,

Jianbo Li

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

Great. Giving accurate solution. very grateful .awesome works. both dax works great @ERD  @v-jianboli-msft 

 

 

@v-jianboli-msft , I like the shorter version, and I relied on the Q1-Q4 values at first, too. However, then I saw that sarath_chandra mentioned 'the cycle contains q1,q2,q3,q4,q5 ... ', so I guess Qn is not a quarter, it's something else.. In this case, try to add Q10 - it won't work as expected.. 

ERD_0-1669970403765.png

As for renaming 'Total' to something else, did't notice that, that's what I might use, thanks!

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

ERD
Super User
Super User

Hello @sarath_chandra ,

Hope this measure helps:

result = 
VAR current_id = SELECTEDVALUE ( 'Table'[ID] )
VAR current_cycle = SELECTEDVALUE ( 'Table 2'[Cycle] )
VAR t =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table'[Cycle] ),
        "@maxrank",
            CALCULATE (
                MAX ( 'Table 2'[Rank] ),
                'Table 2'[Cycle] IN ALLSELECTED ( 'Table'[Cycle] ),
                REMOVEFILTERS ()
            ),
        "@minrank",
            CALCULATE (
                MIN ( 'Table 2'[Rank] ),
                'Table 2'[Cycle] IN ALLSELECTED ( 'Table'[Cycle] ),
                REMOVEFILTERS ()
            )
    )
VAR maxRank = MAXX ( t, [@maxrank] )
VAR minRank = MINX ( t, [@minrank] )
VAR maxCycle =
    CALCULATE (
        MAX ( 'Table 2'[Cycle] ),
        'Table 2'[Rank] = maxRank,
        REMOVEFILTERS ()
    )
VAR minCycle =
    CALCULATE (
        MAX ( 'Table 2'[Cycle] ),
        'Table 2'[Rank] = minRank,
        REMOVEFILTERS ()
    )
VAR _sAmountMaxCycle =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        'Table'[ID] = current_id,
        'Table'[Cycle] = maxCycle
    )
VAR _sAmountMinCycle =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        'Table'[ID] = current_id,
        'Table'[Cycle] = minCycle
    )
VAR result =
    SWITCH (
        SELECTEDVALUE ( 'Table 2'[Cycle] ),
        "XDifference", _sAmountMaxCycle - _sAmountMinCycle,
        IF (
            MAX ( 'Table 2'[Cycle] ) IN ALLSELECTED ( 'Table'[Cycle] ),
            CALCULATE (
                SUM ( 'Table'[Amount] ),
                'Table'[ID] = current_id,
                'Table'[Cycle] = current_cycle
            )
        )
    )
RETURN
    result

ERD_0-1669896402028.png

Please, take into account that I've added a Rank column to the Table 2:

ERD_1-1669896472202.png

 

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Thanks for your great work. HIGHLY appreciative. CAN you please share calculated column -rank column coding for me ??it will be helpful for me to validate properly .plz share it .thanks a lot for yor great work @ERD 

@sarath_chandra , you can create it either in Power Query or in DAX. I just prefer to create Dim tables at Power Query level if possible. To do so in this case:

Create a reference to your Data table and perform needed steps:

 

let
    Source = Table,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Cycle"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    Custom1 = Table.InsertRows(#"Removed Duplicates", 1, {[Cycle = "XDifference"]}),
    #"Added Custom1" = Table.AddColumn(Custom1, "Custom", each [Cycle]),
    #"Split Column by Position1" = Table.SplitColumn(#"Added Custom1", "Custom", Splitter.SplitTextByPositions({0, 1}, false), {"Custom.1", "Custom.2"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Position1",{"Custom.1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","Difference","9999",Replacer.ReplaceText,{"Custom.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"Custom.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Custom.2", "Rank"}})
in
    #"Renamed Columns"

 

In case of calculated column:

1. Create a column with

 

Column =
IF (
    RIGHT ( 'Table 2'[Cycle], LEN ( 'Table 2'[Cycle] ) - 1 ) = "Difference",
    "9999",
    RIGHT ( 'Table 2'[Cycle], LEN ( 'Table 2'[Cycle] ) - 1 )
)

 

2. Change column type to Whole number.

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

v-jianboli-msft
Community Support
Community Support

Hi @sarath_chandra ,

 

Please try:

First create a measure:

DIfference = 
VAR _a =
    SUMMARIZE (
        'Table',
        'Table'[Cycle],
        'Table'[Amount],
        "Rank", VALUE ( RIGHT ( [Cycle], LEN ( [Cycle] ) - 1 ) )
    )
VAR _b =
    MAXX ( _a, [Rank] )
VAR _c =
    SUMX ( FILTER ( _a, [Rank] = _b ), [Amount] )
VAR _d =
    SUMX ( FILTER ( _a, [Rank] < _b ), [Amount] )
RETURN
    _c - _d

Then apply it to the matrix visual:

vjianbolimsft_0-1669872184481.png

Turn off the Rowsubtotals and change the name of Columnsubtotal:

vjianbolimsft_1-1669872251872.png

Final output:

vjianbolimsft_2-1669872270936.png

Best Regards,

Jianbo Li

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

@v-jianboli-msft Hi , Great thanks for your quick response . but you are Q3 and Q1 , ID 24 is working correctly . but ID 25 , it should show negative value (Q3 - Q2)  answer should be (0- 99.90) is -99.90. .Please help me on this . great thanks for your response . highly appreciate for your works . amazed!!

sarath_chandra
Helper III
Helper III

@FreemanZ , @lukiz84 @VahidDM @Greg Deckler .hi all. please help me out from this

i am not sure if i could be of any help, but could you help provide more info about your case to enable others to help you, ideally the pbix file?

https://community.powerbi.com/t5/Desktop/Looking-for-difference-between-two-values-by-filters/td-p/1... use this link and get the pbix file which is already uploaded in community . please help me in this

Im selecting q4 and q1 in filter , it showing the difference *(q4-q1) showing correctly . q4 is higher cycleIm selecting q4 and q1 in filter , it showing the difference *(q4-q1) showing correctly . q4 is higher cycleim selecting Q3 and Q2 .It showing the difference like (-q3-q2) but i need Q3 -Q2 (expected result is xdiffference for ID 24 is +27.89)im selecting Q3 and Q2 .It showing the difference like (-q3-q2) but i need Q3 -Q2 (expected result is xdiffference for ID 24 is +27.89)here also, i need xdifference as Q3 - Q1 (Q3 is higher cycle) but it showing -Q3 -Q1here also, i need xdifference as Q3 - Q1 (Q3 is higher cycle) but it showing -Q3 -Q1

hope i provide the clear requirement ? plz help me out of this. im  not able to share the pbix file . is there any way to share . please help me on this too .

 

thanks a lot .plz clear my queries@FreemanZ

v-jianboli-msft
Community Support
Community Support

Hi @sarath_chandra ,

 

What is Cycle? What is the calculation logic of Difference, and where is Q4?

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

 

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

 

Best Regards,

Jianbo Li

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

https://community.powerbi.com/t5/Desktop/Looking-for-difference-between-two-values-by-filters/td-p/1... 

 

please use this link and click the first question, you will get the pbix file

@v-jianboli-msft  hi, thanks for your reply. table contains cycle , ID , amount as columns . the cycle contains q1,q2,q3,q4,q5 ... I need cycle as filter . and in matrix visual i need cycle in column ,ID in rows . for example , if i selected q4,q3 in filter, it should show the difference in matrix visual .(q4-q3) like that .. if i selecting the q3,q2 in filter , it should show difference in matrix visual (q3-q2). in cycle column is not unique column it will have multiple q1 ,q2,q3,q4....Hope you understand my query

thanks a lot for your reply

sarath_chandra
Helper III
Helper III

@tamerj1 , @Jihwan_Kim please help me for above query 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors