While I am very comfortable with power queries, I am new to using power pivots in connection with data models and would appreciate any insight into the following:
I am trying to use this data model to calculate sales tax per tax category and give me the total sales tax payable to each jurisdiction as a report in power pivot.
Data Model Context:
1. The Tax Zone Key contains combined tax zone names, which are the primary keys of this table and are each listed with up to 8 foreign keys that reference the IDs for the individual tax rate categories that combine to make up this tax zone. (Note: This table functions as a “transaction” table, but the data analysis relies on sales data from the Sales Tax Report for us to calculate any tax amounts.)
2. There are 8 lookup queries that each have a unique primary key/ID column that can be linked to the Tax Zone Key table and that also have associated rate and collection jurisdiction information for that tax ID.
3. Lastly, there is a Sales Tax Report that has the total sales data per tax zone and the taxable sales total. This report is the dynamic variable in the data model. I have it loaded through a power query from a separate worksheet than the Tax Zone Key and other lookup tables. The idea is that the Combined Tax Zone Name in this table can be used to link back to all the related fields in the data model and perform all the measures for the power pivot report on its Taxable Sales (less Credits) column. It functions as somewhat of a joint transaction table to the Tax Zone Key, but is not merged with that table since the tax zone key is largely static, while the Sales Tax Report numbers will change each time we upload a new report.
Objective: Use the “Combined Tax Zone Name” and “Sales Taxable (less Credits)” columns in the Sales Tax Report to pull and calculate the sales tax rate for each of the 8 tax categories and build a power pivot report that can display all the payable jurisdictions (collection jurisdictions) in one column with the total tax duse/payable to each unique jurisdiction totaled in an adjacent column.
What’s Working: The SUMX and RELATE functions used in measures enable me to calculate the taxes due for each of the 8 categories successfully, so the first part of the objective is met.
Problem: In order to display total sales tax due by jurisdiction, it appears that I need to get one condensed list of jurisdictions (with no duplicate jurisdictions) in the “Rows” section of my power pivot. However, since there is a separate “Collection Jurisdiction” column in each of the 8 lookup queries, I cannot just select one of those collection jurisdiction columns from one of the 8 tables in the “Rows” section of the power pivot to display my calculations, as that would exclude the other 7 lists of jurisdictions.
Question: Since I need to reference the jurisdiction columns in the 8 lookup tables in a meaningful way that will allow me to process the data and sum the totals within the data model and I don't want to manually merge all of those jurisdiction columns into a new separate table and using that table as a filter, How can I create a relationship between the various jurisdiction columns in all the lookup tables that would enable me to combine them into one comprehensive list and sum the total tax due? - OR - What kind of DAX formula(s) could I use to perform this task?
I feel like the answer is right in front of me given how extensive and powerful DAX formulas can be, but I just can't seem to get the right angle on this one. Thank you in advance for any assistance you can provide!
I am trying to use this data model to calculate sales tax per tax category and give me the total sales tax payable to each jurisdiction as a report in power pivot.
Data Model Context:
1. The Tax Zone Key contains combined tax zone names, which are the primary keys of this table and are each listed with up to 8 foreign keys that reference the IDs for the individual tax rate categories that combine to make up this tax zone. (Note: This table functions as a “transaction” table, but the data analysis relies on sales data from the Sales Tax Report for us to calculate any tax amounts.)
2. There are 8 lookup queries that each have a unique primary key/ID column that can be linked to the Tax Zone Key table and that also have associated rate and collection jurisdiction information for that tax ID.
3. Lastly, there is a Sales Tax Report that has the total sales data per tax zone and the taxable sales total. This report is the dynamic variable in the data model. I have it loaded through a power query from a separate worksheet than the Tax Zone Key and other lookup tables. The idea is that the Combined Tax Zone Name in this table can be used to link back to all the related fields in the data model and perform all the measures for the power pivot report on its Taxable Sales (less Credits) column. It functions as somewhat of a joint transaction table to the Tax Zone Key, but is not merged with that table since the tax zone key is largely static, while the Sales Tax Report numbers will change each time we upload a new report.
Objective: Use the “Combined Tax Zone Name” and “Sales Taxable (less Credits)” columns in the Sales Tax Report to pull and calculate the sales tax rate for each of the 8 tax categories and build a power pivot report that can display all the payable jurisdictions (collection jurisdictions) in one column with the total tax duse/payable to each unique jurisdiction totaled in an adjacent column.
What’s Working: The SUMX and RELATE functions used in measures enable me to calculate the taxes due for each of the 8 categories successfully, so the first part of the objective is met.
Problem: In order to display total sales tax due by jurisdiction, it appears that I need to get one condensed list of jurisdictions (with no duplicate jurisdictions) in the “Rows” section of my power pivot. However, since there is a separate “Collection Jurisdiction” column in each of the 8 lookup queries, I cannot just select one of those collection jurisdiction columns from one of the 8 tables in the “Rows” section of the power pivot to display my calculations, as that would exclude the other 7 lists of jurisdictions.
Question: Since I need to reference the jurisdiction columns in the 8 lookup tables in a meaningful way that will allow me to process the data and sum the totals within the data model and I don't want to manually merge all of those jurisdiction columns into a new separate table and using that table as a filter, How can I create a relationship between the various jurisdiction columns in all the lookup tables that would enable me to combine them into one comprehensive list and sum the total tax due? - OR - What kind of DAX formula(s) could I use to perform this task?
I feel like the answer is right in front of me given how extensive and powerful DAX formulas can be, but I just can't seem to get the right angle on this one. Thank you in advance for any assistance you can provide!
