Forum Discussion
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!
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
7 Replies
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!
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.
Again the Months table can be change by a date table.
Regards,
MFelix
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
