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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
SLucky
Helper I
Helper I

Total isn't adding up correctly if I add a filter to the visual

Hello,

 

I have three tables.

Property Info table has property_id and R_Date. R_Date is the property active date in the system.

Financial table has revenue and transaction_date. I created a calculated column called Check = if(transaction_date >= R_Date, 1, 0) which limits the transaction_date to be the valid date. 

Third table is a Calendar table.

 

The relationship between Property Info and Financial is property_id 1 to many. Financial table with calendar table is linked by transaction_date and date column in Calendar table. No relationship between Property Info and Calendar table. 

 

I would like to create a measure to show the properties same time last year revenue when 1. if I add Check column as the filter to the visual, and filter it as Check = 1, when selected calendar date >= R_Date it returns last year revenue, otherwise returns blank or zero. 2. If I remove Check filter, it shows last year revenue for all date.

 

To make it simple, below property R_Date is 10/2/2023. I selected 10/1/2023 - 10/4/2023 in calendar table. [CYA - Total Revenue] is a sum(revenue) measure. No "Check =1" filter looks good as it shows last year value. With "Check =1" filter is incorrect as October total isn't adding up correctly. 

 

Summary, this measure works for each individual date but not by month or total if I add Check = 1 to the visual. How can I show the total value correctly even with the filter? Thanks!

SLucky_2-1703004619190.png

 

No "Check =1" filter: looks good

SLucky_1-1703004509547.png

 

With "Check=1" filter: 10/1 is gone which is good while October total is not adding up correctly(has sum up Oct 1st original value)

SLucky_0-1703004486022.png

2 REPLIES 2
ray_codex
Resolver I
Resolver I

The first thing I notice, you are referring to selectedvalue('Calendar table'[date]]). This returns a single value if there is one or blank. With the month oktober you have 31 values, so there is no single value, returning blank, same goes for totals. Totals will return every date in the filtered range.

 

  • I think your issue starts there. You can try something like using HASONEVALUE and create different calculations on True or False, or alter the measure completely. 

Thanks! I change it to Max(Calendar date) and the monthly and total come up but the number isn't correct with Check =1 filter on. 

SLucky_1-1701967387451.png

 

SLucky_0-1701967334645.png

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors