Calculating Revenue by Region

[lastnn30]

Hi

I am not familar with M or DAX. I have 3 tables in Excel (please see below).  I used PowerBI to Get these 3 tables. Went to Modeling and create relationship between these 3 tables (Power BI did it). What I want to create is a report like this:

Region	Revenu
East	?
West	?
North	?
South	?

Revenue for each product is = (1-Discount)* Unit * Price.

Now I do not know how to calculate the revenu and where? in Power Query or in Power BI?

I went to Power Query and opened FactTable and tried to Add-->Custome Column and then tried to write this formula

=(1-[Discount])*[Units]* ?????

here I stucked!! the Price is in another table (dTable1)? How can I finish that formula?

Is there an easier way I can get the revenu for this case using Power BI?  Thank you very much indeed.

 dTabel1

Product	Price
A	100
B	200

dTable2

SalesRep	Region
Alex	West

FactTable

SalesRep	Product	Discount	Unit
Alex	A	0.02	2
Mary	B	0.05	4

[arcanri]

I have a similar situation, but I am unable to use the dTable1[Price] for the Related argument.  I have a many to many relationship which I have handled with a Joiner table.  Is this my issue?

[lastnn30]

Thanks again for your help. I have a question please. I selected FactTable in PowerBI, and then clicked on Table Tools tab in the ribbon and then New Column, then I entered this formula in the Formual bar

Column = (1-FactTable[Discount])*FactTable[Units]*dTable1[price]

the last part "dTable1[price]" was grayed out and it seems that PBI did not recognaize that part. I checked "Model" and found that I already have 1-to-Many relationships between the FactTable and dTable1 and dTable2. Why then PBI does not recognize --> dTable1[price]

Thank you very much.

[ERD · Accepted solution]

@lastnn30 , you need to use RELATED function in this case:

Column = (1-FactTable[Discount])*FactTable[Unit]*RELATED(dTabel1[Price])

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

I am a Ukrainian living in Ukraine. Please, help us to survive. Here are official ways you can support us (accounts with multiple currencies):
https://bank.gov.ua/ua/about/support-the-armed-forces

USD:
BENEFICIARY: National Bank of Ukraine
BENEFICIARY BIC: NBUA UA UX
BENEFICIARY ADDRESS: 9 Instytutska St, Kyiv, 01601, Ukraine
ACCOUNT NUMBER: 400807238
BENEFICIARY BANK NAME: JP MORGAN CHASE BANK, New York
BENEFICIARY BANK BIC: CHASUS33
ABA 0210 0002 1
BENEFICIARY BANK ADDRESS: 383 Madison Avenue, New York, NY 10017, USA
PURPOSE OF PAYMENT: for crediting account 47330992708

Accounts details for other currencies (EUR|GBP|CHF|AUD|CAD|PLN) can be found here: https://bank.gov.ua/ua/about/support-the-armed-forces

[lastnn30]

It is working! Thank you so so much for your help. Great help. Thanks a again.

[lastnn30]

Thank you very much indeed.

[Jos_Woolley]

Hi,

Assuming you have One-to-Many relationships between your two dimension tables and the fact table, this measure:

Revenue =
SUMX(
    FactTable,
    ( 1 - FactTable[Discount] ) * FactTable[Unit]
        * RELATED( dTable1[Price] )
)

which can then be used in a simple table visual together with the Region field from dTable2.

Regards