Dax to calculate sum of a category divided by each line item.

[Anonymous]

Hi Need Help

i want to create a dax which gives percentage distribution of data for example

I have a measure which gives me total invoice. This invoice is distributed with a date category of 01-15, 15-30 so on till +91 days. this category is created using column. I want to calculate total sum of each category divided by invoice line item.

suppose i have categ data as this :-

categ                                   sum

0-15 days	123456
15-30 days	34545353
31-60 days	676456
60-90 days	2454657
91 days	98978768

and invoice data as:

invoice item	price	categ
12	45454	0-15 days
23	67676	0-15 days
45	453443	31-60 days
67	55657	15-30 days

No i want to take sum of table 1 category wise and divide it to each line itme according to its category.

[kentyler]

You should be able to create a relationship between your 2 tables on the categ column. It will be one to many with invoice data on the many side. you can access values from the one side of a relationship in calculations you do for the many side by using the dax function RELATED()

quoting from https://docs.microsoft.com/en-us/dax/related-function-dax

The RELATED function requires that a relationship exists between the current table and the table with related information. You specify the column that contains the data that you want, and the function follows an existing many-to-one relationship to fetch the value from the specified column in the related table. If a relationship does not exist, you must create a relationship.

[Anonymous]

Hi sharing a sample dataset for this with the desired output aslo in that mail. which will help you to understand better. In this sample dataset the firs sheet is invoice sheet which will have invoice details second sheet is exchnage which has date, from currency and currency rates. Now third sheet has spend made by vendor and last sheet has spend by group i.e., 0-15 days and such that. 0-15 days etc. grouping are made by DAX using formulas so i want to devide total amount of spend category by means if the spend date of vendor falls in the cateogry of 0-15 then total sum of 0-15 days spend divided by that vendor spend.

Invoice table

Invoice Number	Date	vendor  Number	vendor payment terms days	Vendor Payment Grouping	Invoice  payment terms days	Currency	Amount
1123	20191001	ABC	30	0-30	10	USD	100
1124	20191002	ABC	30	0-30	20	BRL	200
1125	20191003	ABC	30	0-30	30	INR	300
1126	20191004	DEF	45	31-45	10	USD	200
1127	20191005	DEF	45	31-45	20	BRL	250
1128	20191006	DEF	45	31-45	45	INR	50
1129	20191007	GH	60	46-60	20	BRL	100
1130	20191008	GH	60	46-60	60	BRL	150

exchange table

Date	From Currency	Fx rates
20191001	USD	1
20191001	BRL	1.2
20191001	INR	1.4
20191002	USD	1
20191002	BRL	1.2
20191002	INR	1.4
20191003	USD	1
20191003	BRL	1.2
20191003	INR	1.4
20191004	USD	1
20191004	BRL	1.2
20191004	INR	1.4
20191005	USD	1
20191005	BRL	1.2
20191005	INR	1.4
20191006	USD	1
20191006	BRL	1.2
20191006	INR	1.4
20191007	USD	1
20191007	BRL	1.2
20191007	INR	1.4
20191008	USD	1
20191008	BRL	1.2
20191008	INR	1.4

spend by vendor 

ABC	600
DEF	500
GH	250

spend by group

0-15	600
31-45	500
46-60	250

now if vendor DEF spend fallls in category 0f 0-15 days i want def spend i.e, 500 devided by 0-15 days spendi.e, 600 which total is coming using a measure.