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
dgroyal
Regular Visitor

Counting YAGO data by nationality

I'm trying to create a table that shows counts of student enrollment for a given term (aka semester), by nationality, alongside the count of the same nationality for the YAGO term.  Ultimately, I'd like to show variance (ie 6 if up 6, -5 if down 5) and % variance.

 

I've tried several different approaches (including multiple table occurences, joined on a term -> term yago) but the problem is that nationality breakdown is lost on the YAGO data.  In other words, for every row of data, it shows the TOTAL YAGO count, rather than the count just for that nationality:

 

My current approach is doing this: 

CountYAGO = CALCULATE ( SUM ('FM SIS'[z_CountOne]), ( FILTER (ALL ('FM SIS'), 'FM SIS'[Term] = ( MAX('FM SIS'[Term]) - 100 ))))
 
z_CountOne is just a field with the value of 1, and our term structure is 201801 (for Spring 2018) so subtracting 100 gets me YAGO.

 

My table currently shows:

NATIONALITY | COUNT | COUNT YAGO

China               | 183      | 2248

Saudi Arabia    |  82       | 2248

 

2248 is our TOTAL number of students YAGO, but I want it shown by nationality.

 

Thanks!

 

1 ACCEPTED SOLUTION

@dgroyal i guess attached will help



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

6 REPLIES 6
parry2k
Super User
Super User

@dgroyal please remove ALL( 'FM SIS') from your formula and change it to 'FM SIS'



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks @parry2k .  I did that, but now I get no value in my CountYAGO column.

@dgroyal wihtout knowing how your data is structured it is bit tought, Can you share sample data? What is the purpose deducting 100 ?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Sure, @parry2k .  Deducting 100 from TERM basically does the same things as deducting 1 from YEAR would.

 

Here's some sample data:

https://www.dropbox.com/s/0zwf8v9g1k4sh99/BI_SIS_sample.xlsx?dl=0

https://www.dropbox.com/s/i7wvytr2t8oemg1/BI_Student_sample.xlsx?dl=0

 

I'm trying to show current term and YAGO (aka Term - 100) counts for the SIS file (aka Student in Semester) by nationality, which is in the Student file.  Thanks!


@dgroyal i guess attached will help



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks so much!  I used that calculation in my solution and it works perfectly.  I really appreciate your help!

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.