dax

Question

Hi, I want to compare month wise covarience with the total covarience, messure is as below. It is not workingcovarience = VAR N=COUNTROWS(CALCULATETABLE(VALUES('Custom Calendar Table'[Month]),ALLSELECTED('Custom Calendar Table'[Month])))VAR CurrentAvg=CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AVERAGEX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES('Custom Calendar Table'[Month]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [SelectedMeasureType]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),ALLSELECTED('Custom Calendar Table'[Month])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )VAR PreviousAvg=CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AVERAGEX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES('Custom Calendar Table'[Month]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Previous]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),ALLSELECTED('Custom Calendar Table'[Month])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )VAR Individual_Month_Covarience=DIVIDE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ([SelectedMeasureType]-CurrentAvg)*([Previous]-PreviousAvg),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; N&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )VAR CovTab=SUMMARIZE('Custom Calendar Table',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Custom Calendar Table'[Month],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "@cov",Individual_Month_Covarience)VAR Total_of_AllMonths_Covariance=CALCULATE(SUMX(CovTab,[@cov]))// how to calclate sum of all Indivisual month covarianceRETURN Individual_Month_Covarience/Total_of_AllMonths_Covariance// &nbsp;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 · Answer

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-Forum/ba-p/963216

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/1447523

kenj · Answer

Measures are as belowTotal All Months covariance =VAR N=COUNTROWS(CALCULATETABLE(VALUES('Custom Calendar Table'[Month]),ALLSELECTED('Custom Calendar Table')))VAR CurrentAvg=CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AVERAGEX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES('Custom Calendar Table'[Month]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [SelectedMeasureType]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),ALLSELECTED('Custom Calendar Table')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )VAR PreviousAvg=CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AVERAGEX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES('Custom Calendar Table'[Month]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Previous]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),ALLSELECTED('Custom Calendar Table')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )VAR Individual_Month_Covariance=DIVIDE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ([SelectedMeasureType]-CurrentAvg)*([Previous]-PreviousAvg),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; N&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )VAR CovTable=SUMMARIZE('Custom Calendar Table',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Custom Calendar Table'[Month],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "@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 covarianceRETURN Total_of_AllMonths_Covariance&nbsp; &nbsp; &nbsp;// 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 &nbsp; &nbsp;--------------------------------------------------&nbsp;Individual Month covariance =VAR N=COUNTROWS(CALCULATETABLE(VALUES('Custom Calendar Table'[Month]),ALLSELECTED('Custom Calendar Table')))VAR CurrentAvg=CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AVERAGEX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES('Custom Calendar Table'[Month]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [SelectedMeasureType]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),ALLSELECTED('Custom Calendar Table')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )VAR PreviousAvg=CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AVERAGEX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES('Custom Calendar Table'[Month]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Previous]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),ALLSELECTED('Custom Calendar Table')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )VAR Individual_Month_Covariance=DIVIDE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ([SelectedMeasureType]-CurrentAvg)*([Previous]-PreviousAvg),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; N&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )VAR CovTable=SUMMARIZE('Custom Calendar Table',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Custom Calendar Table'[Month],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "@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 covarianceRETURN Individual_Month_Covariance&nbsp; &nbsp;----------------------------------------------------------------------&nbsp;----------------------------------data----------------------------------- value 2023value 2022 same periodIndivisual Month covarienceTotal All Months covarienceYearMonth£2830771672504171303.24224E+123.24224E+1220231£2603870392356210006.86453E+136.86453E+1320232£3007823892711566511.88183E+131.88183E+1320233£2662901432493379852.25061E+132.25061E+1320234£2914522062663679493.95195E+123.95195E+1220235£3020322012630763916.32242E+126.32242E+1220236£2886583742646656041.36184E+121.36184E+1220237£2928529702666683105.24589E+125.24589E+1220238£2912088282678149074.55357E+124.55357E+1220239

Forum Discussion

dax

2 Replies

Related content

Dax

DAX

DAX Behavior

DAX Formula Help

DAX

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