topic Re: dax in DAX Commands and Tips

dax

kenj — Mon, 01 Jan 2024 05:57:37 GMT

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

)

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

Re: dax

lbendlin — Mon, 01 Jan 2024 20:52:22 GMT

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

Re: dax

kenj — Tue, 02 Jan 2024 06:52:14 GMT

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

)

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

)

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