Forum Discussion
Measure with Variables and Filters
Can you provide a sample of data and your expected results?
Sure. I have uploaded a sample to dropbox. click here
It is a quite simple case. There E-Mailings and Phases. I want to calculate a score for E-Mail A and E-Mail B. The scores are different. Hence on the level of the Initial Phase the value is correct. In the drill down the values are not. Furthermore under Results the same value is shown. This should be 0.
For A and B and the score on the initial level the values should be as such:
Hi raymond -
In the very specific instance you provided, this code gets you what you want, but I doubt it is very scalable since there are still hard-coded values inside.
New Score = var __Email = SELECTEDVALUE(Mailings[Name]) var __Phase = SELECTEDVALUE(Mailings[Phase]) var __A = CALCULATE( DIVIDE( [click-rate] , [click-rate TARGET 20%] , BLANK() ) , Mailings[Phase] IN {"Initial"} , Mailings[Name] IN {"E-Mail_A"}) var __B = CALCULATE( DIVIDE( [click-rate] , [click-rate TARGET 75%] , BLANK() ) , Mailings[Phase] IN {"Initial"} , Mailings[Name] IN {"E-Mail_B"} ) return IF( ISBLANK(__Email), //SELECTEDVALUE returns BLANK if more than one value comes back __A+__B, SWITCH( TRUE(), __Email = "E-Mail_A", __A, __Email = "E-Mail_B", __B ))It's also worth noting that the sample report file you provided, and the 2nd set of detail do not really match the initial question. I realize that you were trying to obscure sensitive data, but if you look at your 2nd and 3rd posts, they're quite different.
Sorry I couldn't be of more help
David
Hi back, you are right, the data is different due to sensitive data. I had to solve the issue differently - in this case I was just using calculated columns and another table.