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/
I think the problem here is in your SumRebate100 measure. The issue at the total level is that doing SUM( col1) * SUM( col2 ) is not the same as doing col1 * col2 then summing the result. To do a row by row multiplication, then sum you would use the SUMX function.
eg.
SumRebate100 = SUMX( 'rebates', 'rebates'[potential rebate] * 'rebates'[target])
This should fix your sumRibit80% measure as that is based off the SumRebate100 measure.
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.
rebateToGet80 = SWITCH(true(),and([%of80Target]>=1,[%OfTarget]<1),[sumRibit80%],0)
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:
rebateToGet80 = SUMX( 'rebates' , IF( [%of80Target]>=1 && [%OfTarget]<1 ,[sumRibit80%] ))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
