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
Ritaf's avatar
Ritaf
Icon for Responsive Resident rankResponsive Resident
7 years ago

 

Hi,

if with && is perfect thanks, but sumx isn't working.

i think it because the target and the rebate is from rebates table.

but sumPurchase is from orders purchase table....

so the measures are:

 

SumPurchase = SUM(factOrdersPurchase[purchase$])

 

 

 

%of80Target = [SumPurchase]/[80%Target]

 

if i use your suggested formula (rebateToGet80 = SUMX( 'rebates' , IF( [%of80Target]>=1 && [%OfTarget]<1 ,[sumRibit80%] )) i get nothing or zero...

 

i tried to change it to my version with your syntax to

 

 

 

if([%of80Target] >=1 && [%OfTarget] <1,[sumRibit80%])

 

but it rtuned me to my problem 0 in totals

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.

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

        thanks a lot, can you recomnd me some materials to read / to see about cross join with dax?