Looking for diff of two values by filters

[sarath_chandra]

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,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)if im selecting q2 and q1 , q2 is higher order cycle ,so q2 is base in difference . so it should show q2-q1

[ERD · Accepted solution]

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

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

[v-jianboli-msft · Accepted solution]

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:

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.

[sarath_chandra]

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

[ERD]

@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.. 

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

[ERD · Accepted solution]

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

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

[sarath_chandra]

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 

[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.

[v-jianboli-msft]

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:

Turn off the Rowsubtotals and change the name of Columnsubtotal:

Final output:

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.

[sarath_chandra]

@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]

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

[FreemanZ]

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?

[sarath_chandra]

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

[sarath_chandra]

Im 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)here 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]

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.

[sarath_chandra]

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

[sarath_chandra]

@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]

@tamerj1 , @Jihwan_Kim please help me for above query