Forum Discussion
Calculating Revenue by Region
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 |
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
6 Replies
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
Thank you very much indeed.
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.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-forcesIt is working! Thank you so so much for your help. Great help. Thanks a again.
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?
