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
akhaliq7
Post Prodigy
Post Prodigy

keepfilters - when aggregating the measure with another column, it brings back the incorrect value

I have written a dax measure that does boolean filters on calculate but brings backs the incorrect value, it uses keepfilters when I add another column the each row shows the correct value, but the total figure is incorrect

 

CALCULATE(
    SUM(Profit),
    KEEPFILTERS(Sales[Order Date] >= start_date),
    KEEPFILTERS(Sales[Order Date] <= end_date
)

 

please note, start_date and end_date above are variables but irrelevant for my question.

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @akhaliq7 ,

Please try:
1. Explicitly Handle Totals:

Measure = 
// This is the total you want to get. Please modify the TotalProfit code as needed.
VAR TotalProfit = CALCULATE(SUM(Profit), ALLSELECTED(Sales))
RETURN
IF(
    HASONEVALUE(Sales[Order Date]),
    CALCULATE(
        SUM(Profit),
        KEEPFILTERS(Sales[Order Date] >= start_date),
        KEEPFILTERS(Sales[Order Date] <= end_date)
    ),
    TotalProfit
)

2. Or try completes context in the code:

MEASURE =
SUMX (
    VALUES ( 'Table'[GroupField] ),
    CALCULATE (
        SUM ( Sales[Profit] ),
        KEEPFILTERS ( Sales[Order Date] >= start_date ),
        KEEPFILTERS ( Sales[Order Date] <= end_date )
    )
)


Why Power BI totals might seem inaccurate - SQLBI
Obtaining accurate totals in DAX - SQLBI

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

View solution in original post

1 REPLY 1
v-cgao-msft
Community Support
Community Support

Hi @akhaliq7 ,

Please try:
1. Explicitly Handle Totals:

Measure = 
// This is the total you want to get. Please modify the TotalProfit code as needed.
VAR TotalProfit = CALCULATE(SUM(Profit), ALLSELECTED(Sales))
RETURN
IF(
    HASONEVALUE(Sales[Order Date]),
    CALCULATE(
        SUM(Profit),
        KEEPFILTERS(Sales[Order Date] >= start_date),
        KEEPFILTERS(Sales[Order Date] <= end_date)
    ),
    TotalProfit
)

2. Or try completes context in the code:

MEASURE =
SUMX (
    VALUES ( 'Table'[GroupField] ),
    CALCULATE (
        SUM ( Sales[Profit] ),
        KEEPFILTERS ( Sales[Order Date] >= start_date ),
        KEEPFILTERS ( Sales[Order Date] <= end_date )
    )
)


Why Power BI totals might seem inaccurate - SQLBI
Obtaining accurate totals in DAX - SQLBI

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.