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

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

Reply
Prakest
Frequent Visitor

Formula not calculating at the aggregated/matrix level

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

Prakest_2-1664417668580.png

Here's a snippet of the data:

Prakest_1-1664417620505.png

 

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")

 

 
6 REPLIES 6
Anonymous
Not applicable

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 IdOrderDefault Sale DateRev Prod Category based OTSales Order Line One TimePrior Yr AdjNew vs RenSalesFlash_AmtARR_AmtGA_AmtWF_Amt
1O-0926/21/2022Y NRenewal     40,047     40,047     40,047 
1O-0926/21/2022 NNRenewal      40,047     40,047 
1O-0926/21/2022 NNRenewal        40,047
1O-0926/21/2022 NNRenewal      (40,047)
1O-0926/21/2022 NNRenewal        40,047
2O-1206/22/2022 NNNew     15,400     15,400     15,400 
3O-1206/22/2022Y NNew     12,000   
3O-1206/22/2022 NNNew      12,000     12,000 
4O-3446/29/2022 NNNew        3,000        3,000        3,000 
4O-3446/29/2022 NNNew           3,000
5O-3446/29/2022 NNNew        3,325        3,325        3,325 
5O-3446/29/2022 NNNew           3,325
6O-3446/29/2022 NNNew   122,155   122,155   122,155 
6O-3446/29/2022 NNNew      122,155
7O-3446/29/2022Y NNew   150,000   
7O-3446/29/2022 NNNew    150,000   150,000 
7O-3446/29/2022 NNNew      150,000
8O-3446/29/2022 NNNew     91,500     91,500     91,500 
8O-3446/29/2022 NNNew        91,500
9O-3446/29/2022 NNNew     45,750     45,750     45,750 
9O-3446/29/2022 NNNew        45,750
10O-3446/29/2022 NNNew     45,750     45,750     45,750 
10O-3446/29/2022 NNNew        45,750
Anonymous
Not applicable

@Prakest ,

may i know how you derived totaldiv4?

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

Anonymous
Not applicable

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

 

 

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 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.

October NL Carousel

Fabric Community Update - October 2024

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