Sum of data within a table with calculates measures from different tables

[indyb]

Hi all, 

I am building a dashboard which has a few tables which are a cut of a overall dataset filtered to the right data. 

I am now trying to put a table together which has measures and trying to SUM this number at the end but struggling to do so. 

The table is as follows : 

I would like a column at the end which totals the SUM of the row per employee ID. 

The employee ID is taken off a staff data table [table 1]

The measures all sit within this staff data table and are as follows and link back to the individual tables : 

CRS Count for CRD 1 = CALCULATE(AVERAGE('CRD 1'[CRD 1 Threshold Count]))

CRS Count for CRD 2 = CALCULATE(AVERAGE('CRD 2'[CRD 2 Threshold Count]))

'CRD 1' and 'CRD 2' are also separate tables. 

Thank you for your help in advance!

Indy

[v-lid-msft]

Hi @indyb ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[Greg_Deckler]

I don't see the table. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Greg_Deckler · Accepted solution]

OK, the image was apparently taking its sweet time to load. So you want a measure like:

Measure = [CRS Count for CRD 1] + [CRS Count for CRD 2]

?

[indyb]

That works! Thanks @Greg_Deckler  I was trying all the complicated ways - SUM(, CALCULATE(, COUNT( etc but didnt think to go back to basics!! 

But furthur on from that, im trying to get this measure as a percentage of the total of measures where a value is populated and have this as 100%.

[v-lid-msft]

Hi @indyb ,

We can try to use the following measure to get the percent value: 

Percent Of Measure =
VAR currentMeasure =
    SUMX ( DISTINCT ( 'Table 1'[employee ID] ), CALCULATE ( [Measure] ) )
VAR measureTotal =
    SUMX (
        CALCULATETABLE ( DISTINCT ( 'Table 1'[employee ID] ), ALLSELECTED () ),
        CALCULATE ( [Measure] )
    )
RETURN
    DIVIDE ( currentMeasure, measureTotal, 0 )

or

Percent Of Measure =
VAR currentMeasure =
    CALCULATE ( [Measure] )
VAR measureTotal =
    CALCULATE ( [Measure], ALLSELECTED () )
RETURN
    DIVIDE ( currentMeasure, measureTotal, 0 )

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to OneDrive For Business and share the link here.

Best regards,