Hi, i have created a sample data and bi file to imitate my actual data set.
I have 2 tables, salestable and targettable.
My sales table have some data for certain months only, wherealese i've created and power queried my target table to apply my target value across the whole month.
I have a datetable to link the [invoice date] from the salestable and the [Start month date] in my targettable
However the target table's values is not show for all the months, which then makes the subtotal confusing.
May refer to the data set and power bi file.
Excel data set file = https://1drv.ms/x/s!AvqyZM_RIenGg2zfzu18uhRx15Zj?e=c5D3Rp
Power bi file = https://1drv.ms/u/s!AvqyZM_RIenGg20OIt8Sphr61C9S?e=MxhluN
Expect result shall refer to the Excel data set file, "Expected result" tab
You're on the right track with a star schema. If you add a Customers dimension table, you'll get the expected result. You can create Customers with Power Query or DAX. Here's a DAX calculated table. You can adjust the logic as necessary (assumption is that all customers are in Salestarget).
Customers = DISTINCT ( Salestarget[Customer name] )
Add Customers to your star schema:
Use Customers[Customer name] in your matrix. If you use Customer name from one of the fact tables, you'll get rows from only that fact table.
You're on the right track with a star schema. If you add a Customers dimension table, you'll get the expected result. You can create Customers with Power Query or DAX. Here's a DAX calculated table. You can adjust the logic as necessary (assumption is that all customers are in Salestarget).
Customers = DISTINCT ( Salestarget[Customer name] )
Add Customers to your star schema:
Use Customers[Customer name] in your matrix. If you use Customer name from one of the fact tables, you'll get rows from only that fact table.
