Dax

[kenj]

I want compare month wise coverience with total coverience. the measure is as below and its not working

covarience =

VAR N=COUNTROWS(CALCULATETABLE(VALUES('Custom Calendar Table'[Month]),ALLSELECTED('Custom Calendar Table'[Month])))

VAR CurrentAvg=CALCULATE(

                    AVERAGEX(

                    VALUES('Custom Calendar Table'[Month]),

                    [SelectedMeasureType]

                    ),ALLSELECTED('Custom Calendar Table'[Month])

                )

VAR PreviousAvg=CALCULATE(

                    AVERAGEX(

                    VALUES('Custom Calendar Table'[Month]),

                    [Previous]

                    ),ALLSELECTED('Custom Calendar Table'[Month])

                )

VAR Individual_Month_Covarience=DIVIDE(

                        ([SelectedMeasureType]-CurrentAvg)*([Previous]-PreviousAvg),

                        N

                        )

VAR CovTab=SUMMARIZE('Custom Calendar Table',

                    'Custom Calendar Table'[Month],

                    "@cov",Individual_Month_Covarience)

VAR Total_of_AllMonths_Covariance=CALCULATE(SUMX(CovTab,[@cov]))

// how to calclate sum of all Indivisual month covariance

RETURN Individual_Month_Covarience/Total_of_AllMonths_Covariance

//  Divide(Individual_Month_Covarience,Total_of_AllMonths_Covariance) to know what portion of the total

// its returnig all 1s when displayed in table with months as row

[gmsamborn]

Hi @kenj 

When calculating [@cov] in CovTab you are using a variable instead of a measure. That means every row in CovTab will have the same value for [@cov].

[v-xiandat-msft]

Hi @kenj ,

Can you give us your expected result graph and sample data, so that we can better solve it for you

Best Regards,

Xianda Tang

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

[123abc]

Your goal is to compare the covariance of each individual month with the total covariance across all months. The formula you have provided seems to be on the right track but requires some adjustments to achieve the desired outcome.

Let's break down the steps to make the necessary adjustments:

1. Calculate the covariance for each individual month.
2. Calculate the total covariance across all months.
3. Divide the individual month's covariance by the total covariance to get the proportion.

Here's a revised version of your DAX measure:

CovarianceProportion =
VAR N =
COUNTROWS (
CALCULATETABLE (
VALUES ( 'Custom Calendar Table'[Month] ),
ALLSELECTED ( 'Custom Calendar Table'[Month] )
)
)

VAR CurrentAvg =
CALCULATE (
AVERAGEX ( VALUES ( 'Custom Calendar Table'[Month] ), [SelectedMeasureType] ),
ALLSELECTED ( 'Custom Calendar Table'[Month] )
)

VAR PreviousAvg =
CALCULATE (
AVERAGEX ( VALUES ( 'Custom Calendar Table'[Month] ), [Previous] ),
ALLSELECTED ( 'Custom Calendar Table'[Month] )
)

VAR Individual_Month_Covariance =
DIVIDE (
([SelectedMeasureType] - CurrentAvg) * ([Previous] - PreviousAvg),
N
)

VAR CovTable =
SUMMARIZE (
'Custom Calendar Table',
'Custom Calendar Table'[Month],
"@cov", Individual_Month_Covariance
)

VAR Total_of_AllMonths_Covariance =
CALCULATE (
SUMX ( CovTable, [@cov] ),
ALLSELECTED ( 'Custom Calendar Table'[Month] )
)

RETURN
DIVIDE ( Individual_Month_Covariance, Total_of_AllMonths_Covariance, 0 )

Make sure to replace [SelectedMeasureType] and [Previous] with the appropriate measures or columns you are using for your calculation. Also, ensure that your 'Custom Calendar Table' has the necessary data for the months you are analyzing.

This revised measure should help you obtain the proportion of each individual month's covariance relative to the total covariance across all months.

[kenj]

Hi Thanks for replying,

I have done this earlier as you suggested fof Total Coverience

VAR Total_of_AllMonths_Covariance =
CALCULATE (
SUMX ( CovTable, [@cov] ),
ALLSELECTED ( 'Custom Calendar Table'[Month] )
)

But it is not working. I cross checked, Its giving the same value as Indivisual Coverience when placed it in a table visual months as row value, where as it should be same total covarience value againts all months.