Forum Discussion

kenj's avatar
kenj
Frequent Visitor
2 years ago

Dax

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

4 Replies

  • 123abc's avatar
    123abc
    Community Champion
    2 years ago

    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's avatar
      kenj
      Frequent Visitor
      2 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

  • gmsamborn's avatar
    gmsamborn
    Super User
    2 years ago

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