Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JustaRookie012
Frequent Visitor

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

1 ACCEPTED SOLUTION
EylesIT
Resolver II
Resolver II

@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:

EylesIT_0-1698169996907.png

 

View solution in original post

1 REPLY 1
EylesIT
Resolver II
Resolver II

@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:

EylesIT_0-1698169996907.png

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.