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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.