Forum Discussion
Calculate a value with multiple tables
I have this table; it shows for a city the monthly percentage that the order value will increase
City Month Percentage
| new york | jan | 0,03 |
| new york | feb | 0,02 |
| new york | mar | 0,02 |
| new york | apr | 0,01 |
| new york | may | 0,03 |
| new york | jun | 0,03 |
| new york | jul | 0,03 |
| new york | aug | 0,03 |
| new york | sep | 0,03 |
| new york | oct | 0,03 |
| new york | nov | 0,02 |
| new york | dec | 0,02 |
In this table I have for a customer (which lifes in a city) the amount of order value. As you can see there is also the date in it
cno date ordval
| 1007 | saturday 2 january 2016 | 21 |
| 1018 | saturday 9 january 2016 | 107 |
| 1018 | saturday 9 january 2016 | 31 |
| 1003 | thursday 21 januari 2016 | 321 |
| 1016 | tuesday 2 february 2016 | 104 |
| 1016 | wednesday 3 february 2016 | 25 |
| 1006 | monday 8 february 2016 | 86 |
I want to know the amount the 'ordval' will increase with the above percentages (first table). I don't know how to do this, to link the month with the date from customer. I do want a new column with these values.
I have this table for the customers, where you can see where the customers lifes.
cno name cityabbrev
| 1003 | George | NY |
| 1006 | Bill | NY |
| 1016 | Steve | NY |
| 1018 | John | NY |
Which is linked to a city table:
city cityabbrev
| new york | NY |
2 Replies
Hi Anonymous ,
The major issue is to establish a relationship between tables.
If the date in the “city month percentage” table is not date type, you need to create a calendar column.
You can try this DAX:
1. Get month of Whole number type
NO_month = SWITCH('City Month Percentage'[month], "jan",1,"feb",2,"mar",3,"apr",4,"may",5,"jun",6,"jul",7,"aug",8,"sep",9,"oct",10,"nov",11,"dec",12,0)2. create a calendar column
DATE_month = DATE(2016,'City Month Percentage'[NO_month],1)3. Format month
T_month = FORMAT('City Month Percentage'[DATE_month],"MMMM")month = FORMAT('cno date ordval'[date],"MMMM" )4. Establish relationships between tables
This is the result:
You also can refer to the .pbix
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply! I found another solution to get the whole month name in the percentage table and I have the month in the ordval table. So I want to make the relation but I only can make a many to many relation between these tables.
I also wonder how you get the percentages in the ordval table.Example (ordval table):
cno date ordval monthname year
1007 saturday 2 january 2016 21 January 2016 1007 friday 26 february 2016 260 February 2016 1007 wednesday 2 march 2016 241 March 2016
(keep in mind that I have for every month order values but it is too much to show)
Example (percentages table):
city month percentage month_full
new york jan 0,03 January new york feb 0,02 February new york mar 0,02 March new york apr 0,01 April chicago jan 0,04 January chicago feb 0,04 February chicago mar 0,03 March As you can see I have more city's, with duplicate months. Maybe that is occuring the problem? (this goes also further, every city has 12 months)
