Forum Discussion
Variance by Contracts
Variance by Customer Name:
I have listing of contracts that are tied to 2% rebates. The Matrix I have "Variance by Customer Name" shows variances. Variance is calculated this way: Forecated Revenue - Recognized Revenue = Variance
Most contracts that have variance are tied to rebates, however there are few that are not. In my matrix I only want to display contracts that are not associated to rebates. I have upload the list of contracts with rebates to Query editor and mapped the relationship.
I want to create a measure that I can apply to my existing Matrix "Variance by Customer Name" that will exclude rebate contracts and keep the remaining as variance. Need help with DAX measurement.
7 Replies
Anonymous
If you put contracts (like ContractID or Contract Name) in a matrix, then you should create a measure that will return 1 when a contract has a rebate and 0 when it doesn't. Then use the measure to filter out the contracts without rebates using the Filter Pane for the visual. This is how you get rid of certain contracts - by creating a filtering measure.
- amitchandak
Super User
Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Amit, I have created new DAX measure, which is not excluding contract that have rebate.
Current measure: Variance Forecasted - Recognized I = [Recognized Revenue I] - [Forecasted Revenue]. Which is including all contracts.
New measure:
Variance Rebates = CALCULATE([Variance Forecasted - Recognized I],(ALLEXCEPT('Reference - Rebates','Reference - Rebates'[Number])))The issue is it's not filtering out the contract that have rebate tied to it.
Amit, I have tried serveral time to post the sample data and output to provide you. Keep receiving the following error message.
Your post has been changed because invalid HTML was found in the message body. The invalid HTML has been removed. Please review the message and submit the message when you are satisfied.
Any idea on how I can fix the error.
Contract on rebate:
Contact NUM CST00321464, Contract Name Northside Hospital, contract has rebate of 2%
Contract NUM CST000123460, Contract Name Piedmond Clinic, contract has no rebate
Data sample:
Contact NUM CST00321464
Northside Hospital
Forecasted Revenue (mnthly) = 15,000
Recognized Revenue (mnthly) = 12,800
Variance between FCST - Recog Rev = 2,200
Since the contract is tied to rebate, I would like to see it excluded
Contract NUM CST000123460
Piedmond Clinic
Forecasted Revenue (mnthly) = 30,000
Recognized Revenue (mnthly) = 24,800
Variance between FCST - Recog Rev = 5,200
Since the contract is tied no rebate, Do not exclude from the output
Output results: Focus on this contract and determine why the variance occured
Contract NUM CST000123460
Piedmond Clinic
Forecasted Revenue (mnthly) = 30,000
Recognized Revenue (mnthly) = 24,800
Variance between FCST - Recog Rev = 5,200
Measure I have in current Matrix:Variance Forecasted - Recognized I = [Recognized Revenue I] - [Forecasted Revenue]
