Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have two tables.
Table 1 Invoice:
Table 2 Reminders:
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)
Year | Period | Reminder sum | Total invoiced same period | Reminder sum percentage of total invoices |
2022 | P1 | 900 | 3.600 | 25% |
2022 | P2 | 200 | 400 | 50% |
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."
Solved! Go to Solution.
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??
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.
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.
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??
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
98 | |
75 | |
63 | |
62 |
User | Count |
---|---|
139 | |
104 | |
104 | |
80 | |
66 |