Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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
@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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
It is working! Thank you so so much for your help. Great help. Thanks a again.
Thank you very much indeed.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |