Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I'm having an issue where the if statement does not seem to recognize a comparison across multiple columns at an aggregated level. If all GA, Salesflash, WF, and ARR columns are the same it should return "Equivalent", otherwise it should return "Different".
Here's a snippet of the data:
This is how I accomplished it when the data was in a different format (pre aggregregated), but it no longer works after additional fields were added. I tried adding helper columns as seen in the first screenshot but that made no difference.
AmountCheck1 =
IF([GA_Amt]=[Totaldiv4]
|| [WF_Amt]=[Totaldiv4]
|| [SalesFlash_Amt]=[Totaldiv4]
|| [ARR_Amt]=[Totaldiv4], "Equivalent",
"Difference")
Hi @Prakest ,
Could you share just a sample data with maybe 10 rows of data ? I can't identify whether the issue is coming from the calculated column or the matrix
BR
@Anonymous
edit: To clarify, this is raw data
Here's a data sample:
Product Id | Order | Default Sale Date | Rev Prod Category based OT | Sales Order Line One Time | Prior Yr Adj | New vs Ren | SalesFlash_Amt | ARR_Amt | GA_Amt | WF_Amt |
1 | O-092 | 6/21/2022 | Y | N | Renewal | 40,047 | 40,047 | 40,047 | ||
1 | O-092 | 6/21/2022 | N | N | Renewal | 40,047 | 40,047 | |||
1 | O-092 | 6/21/2022 | N | N | Renewal | 40,047 | ||||
1 | O-092 | 6/21/2022 | N | N | Renewal | (40,047) | ||||
1 | O-092 | 6/21/2022 | N | N | Renewal | 40,047 | ||||
2 | O-120 | 6/22/2022 | N | N | New | 15,400 | 15,400 | 15,400 | ||
3 | O-120 | 6/22/2022 | Y | N | New | 12,000 | ||||
3 | O-120 | 6/22/2022 | N | N | New | 12,000 | 12,000 | |||
4 | O-344 | 6/29/2022 | N | N | New | 3,000 | 3,000 | 3,000 | ||
4 | O-344 | 6/29/2022 | N | N | New | 3,000 | ||||
5 | O-344 | 6/29/2022 | N | N | New | 3,325 | 3,325 | 3,325 | ||
5 | O-344 | 6/29/2022 | N | N | New | 3,325 | ||||
6 | O-344 | 6/29/2022 | N | N | New | 122,155 | 122,155 | 122,155 | ||
6 | O-344 | 6/29/2022 | N | N | New | 122,155 | ||||
7 | O-344 | 6/29/2022 | Y | N | New | 150,000 | ||||
7 | O-344 | 6/29/2022 | N | N | New | 150,000 | 150,000 | |||
7 | O-344 | 6/29/2022 | N | N | New | 150,000 | ||||
8 | O-344 | 6/29/2022 | N | N | New | 91,500 | 91,500 | 91,500 | ||
8 | O-344 | 6/29/2022 | N | N | New | 91,500 | ||||
9 | O-344 | 6/29/2022 | N | N | New | 45,750 | 45,750 | 45,750 | ||
9 | O-344 | 6/29/2022 | N | N | New | 45,750 | ||||
10 | O-344 | 6/29/2022 | N | N | New | 45,750 | 45,750 | 45,750 | ||
10 | O-344 | 6/29/2022 | N | N | New | 45,750 |
@Anonymous This is where totaldiv4 is coming from:
Totaldiv4 = ([GA_Amt]+[WF_Amt]+[ARR_Amt]+[SalesFlash_Amt])/4
There may be (and probably is) a better method to flag if all 4 "..._Amt" columns are the same.
Hi @Prakest
Try creating a calculated column with
Totaldiv4 =
Var SalesFlash=
IF(Sheet1[SalesFlash_Amt]>=1,1,0)
Var ARRamt=
IF(Sheet1[ARR_Amt]>=1,1,0)
Var GAamt=
IF(Sheet1[GA_Amt]>=1,1,0)
Var WFAmt=
IF(Sheet1[WF_Amt]>=1,1,0)
RETURN
IF(SalesFlash+ARRamt+GAamt+WFAmt>=4,"Equivalent","Different")
BR
Hi @Anonymous
Unfortunately this didn't work. I need to create a flag if any of the columns are different from each other, not just to do a value floor check.
I tried to modify your formula like this but it exhibited the same behavior as before.
TotalDiv4 =
Var Tdiv=
([ARR_Amt]+[GA_Amt]+[WF_Amt]+[SalesFlash_Amt])/4
Var SF=
IF(Tdiv < [SalesFlash_Amt],1,0)
Var ARR=
IF(Tdiv = [ARR_Amt],1,0)
Var GA=
IF(Tdiv = [GA_Amt],1,0)
Var WF=
IF(Tdiv = [WF_Amt],1,0)
RETURN
SF+WF+ARR+GA
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |