Calculate Sum with diferent filters

[JanainaFerreira]

Hi guys,

I have an issue with a calculation in Power BI. Probably this is easier than I can think, but I'm struggling with that.

I need to calculate the Sum(Total Sales) by days filtered in a Slicer. I also need to filter by city and country where the sale was sold.

I created this Measure, but I want to calculate for different cities to show in a Matrix:

teste1 = CALCULATE(SUM('Sales'[Total Sales]/[AT US],'Sales'[Country]="US",'Sales'[City]="Miami")

Country	City	AT
US	Miami	[AT US]
ES	Madrid	[AT EU]
ES	Barcelona	[AT EU]

I want to calculate all the possibilities just in one measure. Is there some way to calculate as a IF formula?

*the measure [AT US] depends on the selected date in a Slicer, which is variable and its works correctly.

[AT US] = The stores in US opens every day, so 30/31 days

[AT EU] = The stores in EU doesn't open on weekend, so 21/22 days

Thanks for any help!!

[JanainaFerreira]

Thank you for your sugestion Sahir_Maharaj,

But didn't work yet. I did what you suggested, but column [AT] shows the value 1 or 0 for each row.

When I did the formula test1, the Sum(Total Sales) was divided by 1 or 0, and not the sum of days which I set on the Slicer.

[Sahir_Maharaj]

This will calculate the sum of total sales for the city "Miami" and also consider the value of [AT] for the country. You can repeat the same steps for other cities as well.

Hope this helps! 🙂

[Sahir_Maharaj]

2. Use the calculated column in your measure:

teste1 = 
CALCULATE(
    SUM('Sales'[Total Sales]),
    'Sales'[City] = "Miami",
    'Sales'[AT]
)

[Sahir_Maharaj]

Here's how you can do it:

1. Create a calculated column with the following formula:

AT =
IF('Sales'[Country] = "US", [AT US],
IF('Sales'[Country] = "ES", [AT EU],
[AT]))

[Sahir_Maharaj]

You can create a calculated column to store the [AT] value based on the country and then use the calculated column in the measure.