Cross table division forumula help

[Anonymous]

I need to calculate a division using values from 2 diffierent tables linked by relationship. The 2 tables are linked by 2 dimension tables: one is a date table and the other a client name table. The calculation is to show the number of Value/OrderNo. (average value per order of the same linked month and same client).

I created the following measure, but it didn't work: 

Division =

DIVIDE(

    calculate(SUM('Table1'[Value]), allexcept(Table1, Table1[Client], Table1[Month])),

    calculate(SUM('Table2'[OrderNo]), allexcept(Table2, Table2[Client], Table2[Month])

))

I tried to use the Filter function and/or Allexcept to make sure the numerator and denumerator

are linked to the correct same month and client. But both not working. What have I done wrong in the code?

Thanks,

[v-juanli-msft]

Hi @Anonymous 

I can make it work on my side.

On my side, two tables have relationships as below

client/month1 = Table1[client]&"/"&Table1[month]

client/month2 = Table2[client]&"/"&Table2[month]

If you still have problems,

Please share some example data with me ofr further analysis.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for the idea of concatenating client and month/period. This is what I didn't have at the time. I used a test model with this concat and then it worked OK.

However, when I trie to use dimension tables for 'client' and 'month', and also 'year', then I couldn't not put two different tables columns in the ALLEXCEPT function. I think this is where it went wrong.

In the real dataset, client names in 2 data tables are not necessarily the same, so a dimension table is used to standardise client names. In fact, there are also other column parameters that I would like to use as a possible filter. Month/year is easier to deal with. I wonder if you have any suggestions if I want to use dimension tables as filters so the resulted chart could be more interactive? Thanks.

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[Anonymous]

Hi, Maggie

Sorry for the late reply, was away. Thank you for taking the time to find and explain the solutions. I was thinking of something along the line but not as clean as your suggestion. Much appreciated.

Thanks,

Alex

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

To make expected visuals and reports, i would suggest you to create proper data models.

I make a test as below, please read though to see if it would help you.

I have three tables

"value table" with columns : client, month, year, department,value

"order table" with columns : client, month, year, department,order

"standardise client table"  with columns : standardise name, invalid name

1.

First open Edit queries, merge "order table" and "value table" to a single table

1.1

merge column "standardise name" from "standardise client table" to"order table"

The same for "value table" , merge column"standardise name"from"standardise client table" to "value table".

1.2 merge columns in "order table"

Merge columns in "value table", the same as above,

Note: In two tables, click on each column on the same order as shown (1,2,3,4)

1.3 Merge two tables "order table" and "value table"

Select "merge queries"->merge as new

then expand "value" and "client" from "value table".

For a good performance when data is huge, you could right click on the table name and uncheck the "enable load"

Close&&apply

fianlly, create a a column in "Merge" table

year/month = [year]&"/"&[month]

Create a calendar table

calendar = FILTER( ADDCOLUMNS(CALENDAR(DATE(2018,1,1),DATE(2018,2,28)),"year/month",FORMAT([Date],"yyyy/m")),DAY([Date])=1)

You could connect this calendar table with "merge1" table with the column "year/month" in both tables.

Then you can add slicers on the report to filter the order/value or any other calculation based on these.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.