Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Cross table division forumula help

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,
 
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

5 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        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.