not showing total on measure column

[nidhs909]

Hello There, 

I have been trying to figure out the issue of not getting a total on calculated measure column, Please need your help. 

Formulas used to calculate columns :

1. 1TO7 Above20K Rate = IF(MergedRows[Employees.JoinDate]<=date(2023,01,01),LOOKUPVALUE(BonusRates[Rate],BonusRates[Grade],MergedRows[Grade],BonusRates[NoOfJobs],"1TO7",BonusRates[Revenue],"Above 20K",BonusRates[JobTitle],MergedRows[BusinessCardTitleRe]),LOOKUPVALUE(BonusRates[Rate],BonusRates[Grade],MergedRows[Grade],BonusRates[NoOfJobs],"NewJoiner",BonusRates[Revenue],"Above 20K",BonusRates[JobTitle],MergedRows[BusinessCardTitleRe]))

2. 1to7 Below20K = SUMX(FILTER(MergedRows, [RankMeasure]<=7 && MergedRows[TotalAmt] <20000),[BonusRateM])

3. BonusAmt = CALCULATE(

        (MergedRows[1to7 Above20K]*SELECTEDVALUE(MergedRows[1TO7 Above20K Rate]))

        +(MergedRows[1to7 Below20K]*SELECTEDVALUE(MergedRows[1TO7 Below20K Rate]))

        +(MergedRows[8to12 Above20K] * SELECTEDVALUE(MergedRows[8TO12 Above20K Rate]))

        +(MergedRows[8to12 Below20K] * SELECTEDVALUE(MergedRows[8TO12 Below20K Rate]))

        +(MergedRows[13+ Above20K] * SELECTEDVALUE(MergedRows[13+ Above20K Rate]))

        +(MergedRows[13+ Below20K] * SELECTEDVALUE(MergedRows[13+ Below20K Rate]))

)

What i need is total on BonusAmt column. 

[Anonymous]

Hi @nidhs909 ,

Just from your screenshot I don't fully know what data is in your table.

There are a lot of column names that do not appear in your DAX code.

Please provide the relevant example data and the specific .pbix file if you can.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomasAndersson]

Theres a lot of columns and rows involved here and a general tip when something doesnt work is trying a smaller dataset/simpler case and trying to figure out how to get a "proof of concept" to work.

That being said, I see that BonusAmt depends on several SELECTEDVALUE(). One thing that very well could happen is that SELECTEDVALUE() works on row level as there on each row is a filter context that ensures there only is one unique value. In the total row, however, all rows are included in the filter context so there might be more than one member within each SELECTEDVALUE(), resulting in a blank. I don't know what dimension you have on rows or what the model looks like, but can it have something to do with that?