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
Anonymous
Not applicable

Masure to get total invoiced from another table where period is in the same year

I have two tables.

 

Table 1 Invoice:

Megaoctane_0-1675686621582.png

 

Table 2 Reminders:

Megaoctane_0-1675686676816.png

 

Now i want to create a visual for reminders per year per period. It is the red ones i am having issues with:

Example for one year, (I have data for many years)

YearPeriodReminder sum

Total invoiced

same period

Reminder sum

percentage of total invoices

2022P19003.60025%
2022P220040050%

 

I have a Date table, so Reminder date and invoice date have a relationship.

 

I am able to create the first three columns, but for the next columns, i am really struggling.
I have tried to write a measure:
TotalInvoiced=CALCULATE(SUM(tbl_Invoice[Amount]),FILTER(tbl_Invoice,tbl_invoice[Period]=
But i am not able to set tbl_invoice[period] =tbl_Reminder[Period].

Tbl_Reminder doesn't show up as a valid choice. If i hardcode it, it says (translated from Norwegian):

"Cant determine single value for the column 'Period', this can happen when it contains too many values without aggregation, for example min, max, count or sum to get a single result."

1 ACCEPTED SOLUTION
DvdH
Helper V
Helper V

Hi. This is because u are trying to filter it based on tbl_invoice.period while this is connected to Reminders.
It cannot filter on multiple values in this way.
Are the 2 tables connected with a relation??

DvdH_0-1675686807428.png

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

No they are not related with any relation. 
I am not entirely sure how to relate them, as the invoicenumber only appears once in tbl_Invoice, but it can appear multiple times in tbl_Reminder. 


Period is also a many to many, and i am not sure what to choose if am to relate these?

Can u show us the ERD? cross out any sensitive information and replace with dummy data.

Anonymous
Not applicable

Megaoctane_0-1675688191782.png

Termin = Period

Fakturadato= Invoice Date

PurreDato = Dunning/reminder date

Following your hint about relationship between these two tables, I created a dummy table, called tbl_Termin (period), and creating a relationship between tbl-Purring(reminder) and tbl_Faktura(invoice), i managet to get the correct data.

Perfect, I was gonna look at the ERD and check if there was a many:many relation needing a middle dummy table with both unique values from either table. Good that u figured it out. Tag me in any other posts if i can help i will.

DvdH
Helper V
Helper V

Hi. This is because u are trying to filter it based on tbl_invoice.period while this is connected to Reminders.
It cannot filter on multiple values in this way.
Are the 2 tables connected with a relation??

DvdH_0-1675686807428.png

 

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.