Hi Jan,

Your first question is definitely a measure, in fact you should calculate measures for the sum of costs and a separate one for the number of patients. You can then use those measures in your patients per clinic measure.

The second question is more difficult to troubleshoot/advise you on without a sample file. Can you create a mock up file from an extract of your data that is anonymised if necessary. It only need to contain an extract of the rows for the tables in question and relevant columns.

Mynda

Hi Mynda, 

Thanks for your quick reply. Here is a mock-up of the issue. 

The raw data file is processed in a PowerQuery macro before it's imported into the data model. The raw data is first extracted from Qlik and stored in separate files for each year and later for additional months.  A Power Query macro retrieves all the files it can find from a selected folder and ready and stores the data in a data model inside the Excel-file.  All the DIM-tables are also stored inside the data model. 

All the best/

Jan 

Hi again, 

 And here is the mock-up file. It got lost in translation

/Jan  

Hi Jan,

Thanks for the file. Unfortunately you can't use RANDBETWEEN to create the Clinic IDs and other dummy data that requires relationships as they are different in each table and therefore when I create relationships between the tables it doesn't work.

You'd be better off copying and pasting an extract of the real data and just deleting the columns that identify it. 

Mynda

Hi Mynda, 

I didn't expect you to perform testing on the actual data, but in hindsight, I do understand that's it's easier for you to work with a functioning rather than a non-functioning mock-up

The connections between the loaded PowerPivot tables have been made in the Data View of the attached file. I hope it works and that I didn't do something similar to "mock-up" this time around.

All the best to you.

Regards, 

Jan 

Hi Jan,

Thanks for sharing your proper data. I see that you haven't unpivoted the Patients data i.e. you have 3 columns containing patient numbers whereas you should have one column with the patient numbers and another with the year/date information. You can use Power Query to fix the layout into a proper tabular format, which is essential for Power Pivot/Power BI. This may be part of the cause of your issues with creating the measures.

Also, your date table should contain consecutive dates, i.e. not just one date for each month, but one for each day in the date range.

For the rebate information, I would bring it into the raw data table using the RELATED function. From there you can either add a calculated column or use the SUMX function to iterate over the rows and perform the calculation.

I hope that points you in the right direction. Let me know if you get stuck.

Mynda

Hi Mynda, 

Regarding the tabular format for the number of patients: I earlier tried to use a tabular format, but then I got three or more rows containing identical clinic IDs, which made it impossible to connect it directly to the "raw data" table. Instead, I had to connect it indirectly via the "Clinics" table. 

When I did try and use the RELATED function, I could not get a match. I guessed at that point it had to do with the indirect connection between the "raw" och "no of patients" tables. I, therefore, decided to merge the clinics and patients' data, as presented to you in the mock-up. 

In this context, I guessed that the pivot filter function would only filter the raw data for a selected year in the dashboard presentation and that I would be able to divide the sum of that filtering with a column cell of my choosing, based not on year, but on the related clinic ID. 

I will try and follow your guide above. Thanks again for your excellent tutorials and your patience with us mere mortals when it comes to Excel excellency. 

Jan 

Hi Jan,

In your case you will have two fact tables, one for costs and one for patients. Both will be related to a dim clinics table. You can refer to the Power Pivot session 6.07 for an example of working with two fact tables to understand it more clearly, especially when it comes to building your PivotTables.

I tested bringing the Lab Rebate Value into the Raw Data table with this formula and it worked in your mock-up file:

=RELATED(Tbl_6_labbtyp[Value])

Mynda

Hi Mynda, 

Thanks for the RELATED formula! I didn't understand it could be used in such a simple way for a calculated column. 

I think I do understand lesson 6.07.  I now also understand that  I will need to use a tabular format for the number of patients data, even though I get multiple clinic ID's. That leaves me with the problem of filtering and getting the right cell data into each  "RAW DATA" table row. Yesterday I tried to create a measure using the RELATED,  RELATEDTABLE and SUMX functions inside PowerPivot to filter the "no of patients" table for "YEAR" and "ClinicID", but came up empty. I tried the same using SUMIFS in a normal Excel table outside the query and it worked fine on my new tabulated "no of patients" sheet. 

=SUMIFS(Dim3_listade!D:D;Dim3_listade!A:A;[@Resultenhetnr];Dim3_listade!E:E;[@Year])

If you have a suggestion for a formula to filter and retrieve row data from the  "no of patients" connected query into a calculated column in the RAW data table, it would be most welcomed.  I've attached a screen dump from the Diagram View.

/Jan 

Hi Jan,

I didn't suggest to use RELATED to bring in the patient numbers to another table, only for the rebate information. The patient numbers should be calculated with a measure in a PivotTable. You don't place the filter data in the measure, instead you allow the filter context of the row and columns of the PivotTable to do the filtering.

Try building a PivotTable using the dim tables for the row and column labels and the value fields form the fact tables. 

Mynda