Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
indyb
Helper III
Helper III

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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%.

 

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.