Forum Discussion

lastnn30's avatar
lastnn30
Post Patron
4 years ago
Solved

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 i...
Need Help
  • ERD's avatar
    ERD
    4 years ago

    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's avatar
lastnn30
Post Patron
4 years ago

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's avatar
ERD
Community Champion
4 years ago

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's avatar
    lastnn30
    Post Patron
    4 years ago

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