Search code examples
databasepowerbidaxmeasure

Sum of calculated measure column to be used in other measure


I currently have a table constructed from a database Port like so, which shows the amount of people in a region (A,B,C,D), how many people there are in that region and how many miles they have run:

Table example

Now, the column Total_Amount_Of_Miles is made by a measure based on the sum of miles people have run, filtered by region). I used the following code for this:

INT(SUMX( DISTINCT(Port[People]),CALCULATE(SUM(Port[Miles_Run]))/CALCULATE(COUNT(Port[People]))))

I was wondering if there is a way to use the total of Total_Amount_Of_Miles in another measure, which I then use to calculate the percentage of the total?

The resulting table would look like this:

Resulting table

I tried to create the measure as follows:

Total_Share = [Total_Amount_Of_Miles]/SUM([Total_Amount_Of_Miles])

But this gives an error as you cannot sum a measure, given that it is context-dependent.

Would really appreciate your help!


Solution

  • Try this measure instead:

    Total share = 
    DIVIDE( 
        [Total Amount of miles], 
        CALCULATE(
            [Total Amount of miles], 
            ALL('Table')
        )
    )
    

    enter image description here