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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Pharms
Frequent Visitor

Sum of a measure

Hi all and thank you for any advice

 

Relatively new to all this and have hit a wall! I am working with SQL as my data source and I have three tables, filtered by the slicer.

 

Pharms_0-1683278420621.png

The name is me so not too worried that is showing! The first table is a countrows of the class code, the second is a countrows of user id in those classes. The third contains a measure that multiplies periods by pupils, by class. All good so far. The totals in the first two tables are fine, and I understand why the total in the third table is what it is, but that is not what I want. My plan is to turn the total off in the third column and what I would like to do is have the sum of the commitments column of the filtered data in a card.

 

I have read quite a few posts but struggling to make sense of it all.

 

ANy help gratefully recieved.

 

Apologies if I have waffled!

1 ACCEPTED SOLUTION

If you're not using a measure then you need CALCULATE to force context transition. Try

Commitments by class v2 =
SUMX (
    VALUES ( 'Timetable Schedule'[txtCode] ),
    CALCULATE ( COUNTA ( 'Set Lists'[txtSchoolID] ) )
        * CALCULATE ( COUNTA ( 'Timetable Schedule'[txtCode] ) )
)

View solution in original post

4 REPLIES 4
Pharms
Frequent Visitor

Amazing, that worked!!! Thank you so much.

johnt75
Super User
Super User

I think you want something like

Commitment by class =
SUMX ( VALUES ( 'Table'[Class] ), [Pupils] * [Periods] )

Thank you for your help so far. I went with

 

Commitments by class v2 = SUMX ( VALUES ( 'Timetable Schedule'[txtCode] ), COUNTA('Set Lists'[txtSchoolID]) * COUNTA('Timetable Schedule'[txtCode]) )
 
but that gave me an even larger total! Think I am going wrong somewhere.

If you're not using a measure then you need CALCULATE to force context transition. Try

Commitments by class v2 =
SUMX (
    VALUES ( 'Timetable Schedule'[txtCode] ),
    CALCULATE ( COUNTA ( 'Set Lists'[txtSchoolID] ) )
        * CALCULATE ( COUNTA ( 'Timetable Schedule'[txtCode] ) )
)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors