Calculation and filter problem

[marcos_osorio]

Hi, sorry to bother you, because I think this is a quite easy problem, but unitl now, cannot solve it.

I have two tables:

TableA                                             TableB

Size                                                   Size

Country                                            Country

Price                                                 Price2

KEY (Size&Country)                         Units

                                                        Key(Size&Coutry)

The two tables are related with the Key Field.

The problem is that I want to show on an Table this:

Size   Country  Price    Price2    PriceUnits

Where PriceUnits is the sum of the Units in TableB who have a Price2 > Price + 1

I tried with:   PriceUnits = calculate(sum(Tableb[Units]), TableB[Price2] > TableA[Price] + 1), I even tried with related but seems to not enable me this option.

Can you help me?

Thank you very much.

[v-frfei-msft]

Hi @marcos_osorio ,

We can create a calculated column in table A as below.

PriceUnits = var a = TableA[Price]+1
return
CALCULATE(SUM(TableB[Unit]),FILTER(TableB,TableB[Price]>a))

Pbix as attached. If it doesn't meet your requirement,  kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[amitchandak · Accepted solution]

Create a new column in table A

PriceUnits = sumx(filter(TableB, TableB[KEY (Size&Country)] = TableA[KEY (Size&Country)]  && TableB[Price2] > TableA[Price] ),Tableb[Units])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[kentyler]

try extracted a variable

 calculate(sum(Tableb[Units]), TableB[Price2] > TableA[Price] + 1),

VAR a_price = SELECTEDVALUE(TableA[Price]) + 1

CALCULATE(SUM(Tableb[Units]),TableB[Price2] > a_price)