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.

Arul's avatar
Arul
Super User
4 years ago

DPH72 ,

try to use ALLEXCEPT,

 

TotVol_L7D =
CALCULATE(
    SUM('00All'[DailyVolTonnes]),
    '00All'[Manufacturer] = "Total",
    '00All'[Date] > Max('00All'[Date]) -  6,
    ALLEXCEPT('00All','00All'[Country])
)

 

Thanks,

Arul

DPH72's avatar
DPH72
Regular Visitor
4 years ago

Hi Arul

Thanks for your reply. Unfortunately this measure calculates the total volume for all countries counting back from each country's individual end date, rather than first calculating the volume in the last 7 days on a by-country basis, and then calculating the sum of these country-specific volumes. Luckily jgeddes was able to provide the desired solution.