Excel Power Pivot: Reporting Packages to include different companies

[HenrikDK]

Hi

I would like to create some reporting packages that include different companies, but I cannot get it to work.

In my example, I have
• A sales table with two columns: CompanyName and SalesAmount
• A Company List: Company A, Company B, Company C and Company D
• A Reporting Package List: Package 1, Package 2 and Package 3
• A Mapping Table that sates that
o Package 1 includes Company A and Company B
o Package 2 includes Company B, Company C and Company D
o Package 3 includes Company C and Company D

I would like a slices with the Reporting Package List that shows the three Reporting Packages and if I select “Package 2” then my data table will only include Company B, Company C and Company D.

I have attached an example with data. The real examples have a lot more companies and reporting packages.

Link to exmaple file https://we.tl/t-V1Zwxi7A1A

Yours sincerely
Henrik

[rwfigtree]

Hi,

This seems to work I think. (see attached)
Not an expert with this sort of thing.
Bound to be a better way.
1. Remove links to sales table.
2. Remove the bridging table.
3. Add calculated column to T_ReportingPackageMappingList
=CALCULATE(T_Sales[SalesAmout],FILTER(T_Sales,T_Sales[ComapnyName]=EARLIER(T_ReportingPackageMappingList[CompanyName])))

[HenrikDK]

Hi rwfigtree

Thanks for your input, but I think you attached the wrong file - I dont see any use of the EARLIER forumla in the attached file and I cannot get it to work.

/Henrik

[rwfigtree]

sorry was on another planet, uploaded wrong file
think i deleted it so did it again

[HenrikDK]

Thanks for the file, rwfigtree.

In my real example, I have alot of rows of data, so I couldn't do it with your suggestion.

I have decided to go another way, so I wont have this problem.

Thanks for your input though.

/Henrik

[Bernard Heymans]

Hi Henrik,

When importing a mass of reports as sources, I work in fases:

-select the files on disk
-sort out the ones that don't fit or are duplicates to keep last version. (I call this one the all_files_list)
-apply an import function on each rox of the files_list
=> you have the table with all data

- create the list of values that will be availlable in slicers
=> list of companies
=> list of reporting packages
(=> eventualy create a calendar table aswel)

- in data model link the 3 tables

If you want to have a beter example, please provide some dummy sources.

functions import: you create an import for one file, and you manualy change it to work against the all_files_list
steps are easy: you have the documents names and path in the all_file_list. you add a function column (your function) with the column containing the documents_path_and_names. and you open the added column.