Forum Discussion
dax
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]),...
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
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 |