Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
walkery
Helper I
Helper I

Dividing Measure Calculation by Column from another table

Hi All, 

 

I have 2 tables in my report, Table A has 3 columns: Month, Office, OfficeCommission and Table B has 3 columns: Month, Office, OfficeRevenue. What I'd like to do is to divide office commissions / total office revenue for each office and month. I tried to create a relationship between the two tables but got an error that there needs to be unique values. Is there a simple calculation to accomplish this goal instead of joining the tables? 

 

Thank you! 

1 ACCEPTED SOLUTION

Hi @walkery,

 

If you are using the Direct Query you cannot create new tables directly check the Using DirectQuery in PowerBI link that as the complete details and limitations.

 

Regarding your second question there is a relationship between both dimension tables and the main tables, then I use the dimension tables on the visuals to create context.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

7 REPLIES 7
MFelix
Super User
Super User

Hi @walkery,

 

Taking into account that you want to have calculations based on date and on office, you need to create two dimension tables:

  • Month table (assuming is only a month and not a date)
  • Office Table 

This 2 tables must have unique values

 

Then just add the following measure:

% Calculated = SUM(Comissions[OfficeComission])/SUM(Revenue[OfficeRevenue])

See result below and a PBIX file with my setup, the Month and Offices comes from the dimensions tables.

 

Untitled.png

 

Again the Months table can be change by a date table.

 

Regards,

MFelix

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix thanks for the suggestion! This is probably a basic question, but how can I create a dimension table? Because I'm using a direct query I don't think that I can create a new table so not sure if that'll effect my ability to create a dimension table?

 

I also saw in your example that then you create a relationship from the main tables to the dimension tables. Is that correct? Still learning the basics of PowerBI so apologies if it's a really stupid question. 

Hi @walkery,

 

If you are using the Direct Query you cannot create new tables directly check the Using DirectQuery in PowerBI link that as the complete details and limitations.

 

Regarding your second question there is a relationship between both dimension tables and the main tables, then I use the dimension tables on the visuals to create context.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hey @walkery

 

Variables are really helpful in situations like these. Try something like:

 

NewMeasure =
VAR CurrentMonth = SELECTEDVALUE('Table A'[Month])
VAR CurrentOffice = SELECTEDVALUE('Table A'[Office])
VAR CurrentOfficeCommission =
CALCULATE(
   SUM('Table A'[OfficeCommission]),
   FILTER(
      ALLSELECTED(Table A),
      'Table A'[Office] = CurrentOffice && 'Table A'[Month] = CurrentMonth
   )
)
VAR CurrentOfficeRevenue =
CALCULATE(
   SUM('Table B'[OfficeRevenue]),
   FILTER(
      ALLSELECTED(Table B),
      'Table B'[Office] = CurrentOffice && 'Table B'[Month] = CurrentMonth
   )
)
RETURN
DIVIDE(CurrentOfficeCommission,CurrentOfficeRevenue)
   

Hope this helps,

Parker

Hi @Anonymous thanks for the quick reply! I tried your solution, but got an error "Function 'Filter' is not supported in this context in DirectQuery mode". Is there a workaround for that? Thanks again! 

Anonymous
Not applicable

Ah DirectQuery lmitations... Try checking the "Allow unrestricted measures in DirectQuery mode" box in Options.

 

DirectQueryOptions.PNG

Thanks @Anonymous. That worked with not getting the error but the new measure is still just returning blank values. Do I have to have my data structured in any specifc way to get the measure to return actual values? 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.