How to create variance field and variance

[rcamatchee]

 

Hi Guys,

I have created three tables Actsales, BdtSales and LYSales that contains duplicates as they relate to sales transactions for actuals, last year compared to budget.  I have indeirectly link them by creating dimension tables.

Although, I have created a nice matrix, yet I'm unable to create variance columns e.g Actual v Budget and Actual v Last year as well.

Can you show me the way, thank you

Regards

Rama Camatchee

[v-shex-msft]

Hi @rcamatchee,

For your requirement, you can try to use filter to extract related number list and use 'VARX.P' function to calculate with extracted list.

Sample measure:

Variance Measure =
VAR budSales =
    CALCULATETABLE (
        VALUES ( budSales[BdtSales] ),
        'use relationship to filtler budsales table'
    )
VAR actSales =
    CALCULATETABLE (
        VALUES ( actSales[ActSales] ),
        'use relationship to filtler actSales table'
    )
VAR lyactSales =
    CALCULATETABLE (
        VALUES ( lyactSales[LYSales] ),
        'use relationship to filtler lyactSales table'
    )
RETURN
    VARX.P ( UNION ( budSales, actSales, lyactSales ), [BdtSales] )

Regards,

Xiaoxin Sheng

[rcamatchee]

Hi Xiaoxin Sheng,

Thank you.

I have created a new measure under matrix and inserted the DAX, but it returns an error despite that I have properly linked the three tables under dimtables.

Kindly assist.

Regards

Rama

[v-shex-msft]

Hi @rcamatchee,

These error part need to be replaced with your own filters to use current item to get related table records.

Regards,

Xiaoxin Sheng

[rcamatchee]

Hi Xiaoxin Sheng,

What do you mean by  own filters can you give me an example.

At the moment, I don't need any filters.

Please note that you have correctly inserted the field names in the DAX script

Regards

Rama Camatchee

[v-shex-msft]

Hi @rcamatchee,

For example, your visualization is based on salesperson column and related sales from three different tables, you can modify formula like below:

Variance Measure =
VAR _budSales =
    CALCULATETABLE (
        VALUES ( 'budSales'[BdtSales] ),
        VALUES ( 'Salesperson_Dim'[Salesperson] )
    )
VAR _actSales =
    CALCULATETABLE (
        VALUES ( 'actSales'[ActSales] ),
        VALUES ( 'Salesperson_Dim'[Salesperson] )
    )
VAR _lyactSales =
    CALCULATETABLE (
        VALUES ( 'lyactSales'[LYSales] ),
        VALUES ( 'Salesperson_Dim'[Salesperson] )
    )
RETURN
    VARX.P ( UNION ( _budSales, _actSales, _lyactSales ), [BdtSales] )

Regards,

Xiaoxin Sheng