dax

[kenj]

Hi, I want to compare month wise covarience with the total covarience, messure is as below. It is 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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[kenj]

Measures are as below

Total All Months covariance =

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

VAR CurrentAvg=CALCULATE(

                    AVERAGEX(

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

                    [SelectedMeasureType]

                    ),ALLSELECTED('Custom Calendar Table')

                )

VAR PreviousAvg=CALCULATE(

                    AVERAGEX(

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

                    [Previous]

                    ),ALLSELECTED('Custom Calendar Table')

                )

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]))

// how to calclate sum of all Indivisual Month Name covariance

RETURN Total_of_AllMonths_Covariance

 // Divide(Individual_Month_Covariance,Total_of_AllMonths_Covariance)

// to know what portion of the total

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

--------------------------------------------------

Individual Month covariance =

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

VAR CurrentAvg=CALCULATE(

                    AVERAGEX(

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

                    [SelectedMeasureType]

                    ),ALLSELECTED('Custom Calendar Table')

                )

VAR PreviousAvg=CALCULATE(

                    AVERAGEX(

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

                    [Previous]

                    ),ALLSELECTED('Custom Calendar Table')

                )

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]))

// how to calclate sum of all Indivisual Month Name covariance

RETURN Individual_Month_Covariance

   ----------------------------------------------------------------------

----------------------------------data-----------------------------------

value 2023	value 2022 same period	Indivisual Month covarience	Total All Months covarience	Year	Month
£283077167	250417130	3.24224E+12	3.24224E+12	2023	1
£260387039	235621000	6.86453E+13	6.86453E+13	2023	2
£300782389	271156651	1.88183E+13	1.88183E+13	2023	3
£266290143	249337985	2.25061E+13	2.25061E+13	2023	4
£291452206	266367949	3.95195E+12	3.95195E+12	2023	5
£302032201	263076391	6.32242E+12	6.32242E+12	2023	6
£288658374	264665604	1.36184E+12	1.36184E+12	2023	7
£292852970	266668310	5.24589E+12	5.24589E+12	2023	8
£291208828	267814907	4.55357E+12	4.55357E+12	2023	9