Forum Discussion

Arendp's avatar
Arendp
Helper III
6 years ago
Solved

sum divided by count

Hello I have a problem with a Dax calc.

I need to divide the sum of Metingtest 2 by count Metingtest. That results in Metingtest3. And that result is correct.

However in the bottom , you see 52488 .That is the result of the total divided by total count. (264382263/5037)

But I need to have the sum of Metingtest3 (so all results of that column summed up)

How can I accomplish that?

So calculation of Metingtest3 = sum(budgetrow)/Count(budgetrow)

 

Hopefully I am explaning myself in a good way... 🙂

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Arendp ,

     

    I think it needs to be something like this: 

     

    AvgRowAndTotal = 
    // My Dummy MetingTest3Berekening = Divide(sumx ( sales, Sales[Quantity] * Sales[Net Price]),sum(Sales[Quantity]))
    Var RowOrTotal = 
    IF ( ISFILTERED('Product'[Category]),     -- replace Product[Category] with your own fields
    Divide(sumx ( sales, Sales[Quantity] * Sales[Net Price]),sum(Sales[Quantity])), 
    SUMX(
    Summarize (                 -- create temporary table with the same subtotals
     Sales,                     -- you can check them in DAX studio
    'Product'[Category], 
    "rowtotal", Divide(sumx ( sales, Sales[Quantity] * Sales[Net Price]),sum(Sales[Quantity]))
    )
    , [rowtotal])
    )
    return RowOrTotal

    Hope this helps, 

     

    Jan 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Arendp ,

     

    I think it needs to be something like this: 

     

    AvgRowAndTotal = 
    // My Dummy MetingTest3Berekening = Divide(sumx ( sales, Sales[Quantity] * Sales[Net Price]),sum(Sales[Quantity]))
    Var RowOrTotal = 
    IF ( ISFILTERED('Product'[Category]),     -- replace Product[Category] with your own fields
    Divide(sumx ( sales, Sales[Quantity] * Sales[Net Price]),sum(Sales[Quantity])), 
    SUMX(
    Summarize (                 -- create temporary table with the same subtotals
     Sales,                     -- you can check them in DAX studio
    'Product'[Category], 
    "rowtotal", Divide(sumx ( sales, Sales[Quantity] * Sales[Net Price]),sum(Sales[Quantity]))
    )
    , [rowtotal])
    )
    return RowOrTotal

    Hope this helps, 

     

    Jan 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

    Are they columns or measure?

     

    For column you have hasonevalue function.

     

    There are more threads for total is wrong on community you can check those.

     

    Thanks,

    Pravin