topic Re: Looking for diff of two values by filters in DAX Commands and Tips

Looking for diff of two values by filters

sarath_chandra — Tue, 29 Nov 2022 16:20:42 GMT

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

Re: Looking for diff of two values by filters

sarath_chandra — Tue, 29 Nov 2022 16:30:35 GMT

tamerj1 , Jihwan_Kim please help me for above query

Re: Looking for diff of two values by filters

v-jianboli-msft — Wed, 30 Nov 2022 07:13:31 GMT

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.

Re: Looking for diff of two values by filters

sarath_chandra — Wed, 30 Nov 2022 07:54:57 GMT

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

Re: Looking for diff of two values by filters

sarath_chandra — Wed, 30 Nov 2022 11:56:12 GMT

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

Re: Looking for diff of two values by filters

FreemanZ — Wed, 30 Nov 2022 13:35:31 GMT

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?

Re: Looking for diff of two values by filters

sarath_chandra — Wed, 30 Nov 2022 14:55:09 GMT

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

Re: Looking for diff of two values by filters

sarath_chandra — Wed, 30 Nov 2022 15:45:42 GMT

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

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

Re: Looking for diff of two values by filters

sarath_chandra — Wed, 30 Nov 2022 15:47:49 GMT

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

Re: Looking for diff of two values by filters

v-jianboli-msft — Thu, 01 Dec 2022 05:25:58 GMT

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.

Re: Looking for diff of two values by filters

sarath_chandra — Thu, 01 Dec 2022 06:31:47 GMT

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!!

Re: Looking for diff of two values by filters

ERD — Thu, 01 Dec 2022 12:08:35 GMT

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:

Re: Looking for diff of two values by filters

sarath_chandra — Fri, 02 Dec 2022 03:17:05 GMT

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

Re: Looking for diff of two values by filters

v-jianboli-msft — Fri, 02 Dec 2022 06:47:37 GMT

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.

Re: Looking for diff of two values by filters

ERD — Fri, 02 Dec 2022 08:15:18 GMT

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.

Re: Looking for diff of two values by filters

ERD — Fri, 02 Dec 2022 08:44:40 GMT

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!

Re: Looking for diff of two values by filters

sarath_chandra — Mon, 05 Dec 2022 15:09:36 GMT

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