Forum Discussion

DPH72's avatar
DPH72
Regular Visitor
4 years ago
Solved

Remove filter on one column while retaining filter on another

I have table containing daily volumes by country and date. Each country has a different end date. I want to calculate the sum of the volumes that each country has sold in the past 7 days, using the l...
Need Help
  • jgeddes's avatar
    jgeddes
    4 years ago

    To replicate my understanding of your concern I created the dataset

    I then wrote a measure for lastSevenDaysVolume

    LastSevenDaysVolume =
    SUMX(
        SUMMARIZE(lastSevenDays,lastSevenDays[Country], "_sum", CALCULATE(SUM(lastSevenDays[Volume]),lastSevenDays[Date] > datevalue(MAX(lastSevenDays[Date])-7))),
        [_sum]
    )
    and a measure for Total Volume
    TotalVolumeL7D =
    CALCULATE(
        SUMX(
            SUMMARIZE(lastSevenDays,lastSevenDays[Country], "_volume", [LastSevenDaysVolume]),
            [_volume]
        ),
        ALL(lastSevenDays[Country])
    )
    with the resulting table

    This may not be the exact solution to your concern but it should point you in the right direction.

jgeddes's avatar
jgeddes
Super User
4 years ago

To replicate my understanding of your concern I created the dataset

I then wrote a measure for lastSevenDaysVolume

LastSevenDaysVolume =
SUMX(
    SUMMARIZE(lastSevenDays,lastSevenDays[Country], "_sum", CALCULATE(SUM(lastSevenDays[Volume]),lastSevenDays[Date] > datevalue(MAX(lastSevenDays[Date])-7))),
    [_sum]
)
and a measure for Total Volume
TotalVolumeL7D =
CALCULATE(
    SUMX(
        SUMMARIZE(lastSevenDays,lastSevenDays[Country], "_volume", [LastSevenDaysVolume]),
        [_volume]
    ),
    ALL(lastSevenDays[Country])
)
with the resulting table

This may not be the exact solution to your concern but it should point you in the right direction.

DPH72's avatar
DPH72
Regular Visitor
4 years ago

Thank you jgeddes, this worked!