Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
AmenYves
Frequent Visitor

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

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

 

AmenYves_0-1656072550478.png

 

Formula used to calculate

 

AmenYves_1-1656072753546.png

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

 

AmenYves_2-1656072842561.png

 

Any Ideas? Thank you very much in advance!

Kind regards

Daniela

1 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

yingyinr_1-1656403983883.png

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

yingyinr_0-1656403954394.png

3. Create a measure as below:

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

yingyinr_2-1656404053673.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
AmenYves
Frequent Visitor

Hello @rajulshah 

 

Tables in Power BI

 

AmenYves_0-1656075372073.png

2nd Table in power BI

AmenYves_1-1656075424310.png

 

"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

 

AmenYves_2-1656075733007.png

 

Hope this helps.

 

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

yingyinr_1-1656403983883.png

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

yingyinr_0-1656403954394.png

3. Create a measure as below:

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

yingyinr_2-1656404053673.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rajulshah
Super User
Super User

Hello @AmenYves ,

 

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.