Variance by Contracts

[Anonymous]

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.

[Anonymous]

@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.

[Anonymous]

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]

[Anonymous]

@Anonymous 

You can easily paste an excel table straight into the body of the message... so please do it.

[Anonymous]

That did not work daxer.

[Anonymous]

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.

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

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.