calculating sum for different rows depending on column value (date information)

[AmenYves]

Hello, 

I would need support as power BI newbie for maybe a very simple task....🤔. 

I have two tables, each tables have a column with a date (date when data were loaded) and I am trying to create a line chart with data trends. This is a calculated measure with a division between two tables with the respective row content. 

If I am just using the divde function I am getting a trend per date of data loaded. But the values are wrong. The results with a calculate-Sum combination are correct but every date is showing the same value, it only calculates the latest date..... Screenshot of the correct value but same value for all datapoints below

Formula used to calculate

What I was hoping to get (here results with DIVIDE function without calculate but wrong values calculated)

Any Ideas? Thank you very much in advance!

Kind regards

Daniela

[AmenYves]

Hello @rajulshah 

Tables in Power BI

2nd Table in power BI

"Active Focus Group" and "Active" are set to "count" in properties (originally set to sum, doesn't make a difference) and are supposed to be 

1. summarized by date and

2. divided with "Sum from date 1 Table 1" / "Sum from date 1 Table 2".

The two tables are linked via one parameter only (here linked relationship) and the relationship setting is as such

Hope this helps.

[v-yiruan-msft · Accepted solution]

Hi @AmenYves  ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a date dimension table as below

2. Create relationship among the date dimension table, first table and second table

3. Create a measure as below:

Measure = DIVIDE(SUM('First Table with information'[ACTIVE Focus Group]),SUM('Second Table with information'[Active]))

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[rajulshah]

Hello @AmenYves ,

Can you please share the sample structure to better understand the issue?