Trying to recreate a measures calculation to either a calculated table or calculated column

[Anonymous]

Hello everyone,

My problem is a big one that I have been struggling with for a while. To put the question is context I have 3 columns that are essentially used in the calculation of the measure:

Column 1 where the values Cash

Column 2 where the values Names

Column 3 where the values are Names and Dates Combined 

When creating the measure I was able to use this formula to get the desired calculation: 

measure = DIVIDE(SUM(Table[Column1]), DISTINCTCOUNT(Table[Column3]))

Then I was able to get the desired calculation by dragging and dropping the measure and Column 2 into the same table thus creating this measure by Name.

The problem is I have been trying to recreate this measure as a calculated column or as a calculated table but have not had any luck using groupby and summarize functions as they have come out to be much larger values than the actual calculations.

I was wondering if anyone could possibly help me out with this complex problem I've been having.

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Calculated column:

New Column =
DIVIDE (
    CALCULATE ( SUM ( Table[Column1] ), ALLEXCEPT ( Table, Table[Column2] ) ),
    CALCULATE (
        DISTINCTCOUNT ( Table[Column3] ),
        ALLEXCEPT ( Table, Table[Column2] )
    )
)

Calculated table:

New Table =
ADDCOLUMNS (
    SUMMARIZE (
        Table,
        Table[Column2],
        "Sum", SUM ( Table[Column1] ),
        "Count", DISTINCTCOUNT ( Table[Column3] )
    ),
    "divide", [Sum] / [Count]
)

Best regards,

Yuliana Gu

[Anonymous]

@v-yulgu-msft Nevermind, your answer works. I was having some troubles in other areas but that is being sorted out. Thank you so much for a great answer!

[Anonymous]

Hello,

Thank you for replying to my post! Both solutions didnt work and when looking at the sum and count for the calculated tables they were way bigger than the sum and count for the measure used to calculate the average. 

The excel function that I am trying to replicate in Power BI is pretty much a pivot table of Column2 as the rows and Column3 as the columns and Column1 as the values. Then I wanted to divide the total sum of each of the rows of the tables by the count of how many values there were in that row and this would make the average for each person in Column2

Please let me know if that made sense or if you think that is not possible in Power BI.