Forum Discussion
summarise measures
The product documentation covers the basics https://docs.microsoft.com/en-us/dax/crossjoin-function-dax
And the following article might also be of interest https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
Ritaf wrote:Hi,
firstly thanks a lot , it fixed me a part of my problem, as you said a totals of potential rebates is correct now.
but the totals of real rebate is still 0.
This is just a variation of the same issue. But I could not suggest a fix for rebateToGet80 as you did not post the expression for that.
A switch with a single condition can be simplified using an IF and I prefer using the && operator instead of the and() function as I think it makes it easier to read. I'd also suggest not returning a explicit 0 if using the SUMX pattern as blank values get automatically filtered out by Power BI, but 0 can cause extra rows to appear in your visuals that you may not want.
So I would suggest changing the expression as follows:
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
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.
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)returnSUMX(_Tbl,CALCULATE([sumRibit80%]))it works :)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.