Forum Discussion

JustaRookie012's avatar
JustaRookie012
Frequent Visitor
2 years ago
Solved

Question regarding fixed value

Dear community,

 

I have the following simplified Table

CompanyProjectPeriodValue1Value2
AA131020
AA131020
AB13 20
AB13 20
AC131020
AC132020
AD13 20
AD13 10

 

I want to create a measure that returns me a fixed value by dividing the sum of non blank value1 and the sum of non blank value2 whereas the sum of these values is grouped by Company and Period. 

Here is what I have created to "achieve" the above desired result

 

 

 

AVERAGEX (
    SUMMARIZE (
        CALCULATETABLE (
            'Table',
            'Table'[Value1] <> BLANK ()        
),
       'Table'[Company],
        'Table'[Period],
        "Result",
            VAR SumValue1=
                SUM ( 'Table'[Value1] )
            VAR SumValue2 =
                SUM ( 'Table'[Value2] )
            RETURN
                SumValue1/ SumValue2
    ),
    [Result]
)

 

 

And I get the following as a result if I use this measure, it seems that measure is calculating row by row

CompanyProjectPeriodValue1Value2Result
AA1310200,5
AA1310200,5
AB13 200
AB13 200
AC1310200,5
AC1320201
AD13 200
AD13 100
Total    0,33333

Sum(Value1) = 50

Sum(Value2) = 150

Result = 50/150= 0,333333 

 

Whereas I expected the fixed value based on the sum of non blank value1 and sum of non blank value2 should be applied 

CompanyProjectPeriodValue1Value2Result
AA1310200,3333
AA1310200,3333
AB13 200,3333
AB13 200,3333
AC1310200,3333
AC1320200,3333
AD13 200,3333
AD13 100,3333
Total    0,3333

 

Any help would be appreciated!

 

Kind regards,

Rookie12

  • EylesIT's avatar
    EylesIT
    2 years ago

    JustaRookie012, here is my suggested solution.

    Change the DAX of your Result measure to this:

    Result = 
    CALCULATE(
        DIVIDE(SUM(YourTable[Value1]), SUM(YourTable[Value2])),
        ALL(YourTable)
    )

     

    This gives me the following output:

     

1 Reply

  • EylesIT's avatar
    EylesIT
    Resolver II
    2 years ago

    JustaRookie012, here is my suggested solution.

    Change the DAX of your Result measure to this:

    Result = 
    CALCULATE(
        DIVIDE(SUM(YourTable[Value1]), SUM(YourTable[Value2])),
        ALL(YourTable)
    )

     

    This gives me the following output: