Hei,
I have a tricky issue. Imagine you have 4 tables. Transaction, Property,Region and general_info.
Transaction and general_info has a relation one to one. Transaction to Property one to many, Property and region many to many relationship ( bridge table in the midle ) (same region/adress kan have different properties, and vise versa) . One transaction can have just one property or several. Each property has a price that normaly is in each property row on table property. general_info has the sum of properties prices in that ttransaction. Sometimes there are properties thas has no specific price so i calculate them getting total price of transaction minus proprieties in transaction with known price and the result i divide by number of properties i do not have price.
when using the measure on DONUT CHART it works perfect if I have just ONE transaction in the datasett. If I have more DAX wil sum total price of the two transactions. It will sum the total price of each proprieties in both transactions. get the difference between both and divide by the number of properties that has no price in both transactions. The price of each property will be in that way FAIL since the dax do not take into account each transaction but all at same time.
please se dax formual I use :
pris_product_counties =
VAR PriceSum = SUM('transaksjonsliste t_produkt'[pris_mnok])
Var Pricesumall = CALCULATE( SUM('transaksjonsliste t_produkt'[pris_mnok]),all('transaksjonsliste t_kgb_f_k'), 'transaksjonsliste t_transaksjon')
VAR totalNumProducts = Calculate(COUNTROWS('transaksjonsliste t_produkt'),all('transaksjonsliste t_kgb_f_k'), 'transaksjonsliste t_transaksjon')
VAR totalNumProductswhithoutprice = Calculate(COUNTROWS('transaksjonsliste t_produkt'),all('transaksjonsliste t_kgb_f_k'), 'transaksjonsliste t_transaksjon',ISBLANK('transaksjonsliste t_produkt'[pris_mnok]))
VAR TransactionPrice = SUM('transaksjonsliste t_generelt_t'[pris_mnok])
VAR diffprice = (TransactionPrice-Pricesumall)
VAR nb_uten_price_on_county=Calculate(COUNTROWS('transaksjonsliste t_produkt'), 'transaksjonsliste t_transaksjon',ISBLANK('transaksjonsliste t_produkt'[pris_mnok]),ALL(),KEEPFILTERS('transaksjonsliste t_kgb_f_k'))
RETURN
IF(ISNUMBER(PriceSum), PriceSum+(nb_uten_price_on_county*(diffprice/totalNumProductswhithoutprice)), diffprice/totalNumProductswhithoutprice)
Do you have any suggestion about which changes should be done to make formula behave as intended ?
thanks in advance
