Forum Discussion
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 rebated on 80% , 100% and 120% of target. I made 3 tables for every stage of target and have 2 problems:
1. total rebate in every table is incorrect.
2. if i want to see a total rebate for supplier anf it shows me 0 in table or error in card.
I attached a model schema, an example of one from 3 tables of report , table of rebates confitions and the formulas i used, hope i explained my self clearl and you can help me to fix them.
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/
9 Replies
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%] ))
