cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
indyb
Helper II
Helper II

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

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 : 

 

Capture.PNG

 

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
1 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]

?

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
v-lid-msft
Community Support
Community Support

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,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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]

?

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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
Community Support
Community Support

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors