Forum Discussion

Ritaf's avatar
Ritaf
Icon for Responsive Resident rankResponsive Resident
7 years ago
Solved

summarise measures

Hi , i made a report for monitoring rebates for purchase. We get Rebate from supllier , by purchasing products of special manifacturers.  every manigacturer have his target and rebate. We getting a ...
Need Help
Tips and Tricks
d_gosbell's avatar
d_gosbell
Icon for Super User rankSuper User
7 years ago

Hmm, with measures from 2 different facts doing a SUMX just over rebates is not going to work. You might be able to do a crossjoin of the values of the keys from the 3 different dimension tables, then do a SUMX over that derived table, but I would be worried about the performance of a solution like that.

 

Is there any way of looking up the rebate for a given row in the purchase orders table?

 

If so you could probably create a calculated column in purchase orders or do a SUMX over that looking up the rebate amount.

Ritaf's avatar
Ritaf
Icon for Responsive Resident rankResponsive Resident
7 years ago

Hi,

Developer which working with our company is helped me with a formula:

rebateToGet80 =
var _Tbl = FILTER(CROSSJOIN(VALUES('PBI DWH_Dim_Supplier_view'[קוד ושם ספק]),VALUES('PBI DWH_Dim_Manufacturers'[קוד ושם יצרן])),[%of80Target] >=1 && [%OfTarget] <1)
return
SUMX(_Tbl,CALCULATE([sumRibit80%]))
it works :)
  • d_gosbell's avatar
    d_gosbell
    Icon for Super User rankSuper User
    7 years ago

    Cool, that's the exact approach I was thinking of when I said "a crossjoin of the values of the keys from the 3 different dimension tables, then do a SUMX over that derived table". I'm glad you found a solution.