calculate sum only for products from a particular category

[Donna123]

Hello Community,

I need a hint how to calculate sum nut only for a given group of products - I mean "cost maintenance" should be calculated only for Vehicles from company "PE" and should be 0 for "AB" and others, after that it should be also excluded from "total_cost_t".

"cost_maintenance" is an average cost calculated with below formula (sum of maintenance cost divided by vehicles from PE category):

cost_maintenance =

VAR maintenance_sum =

CALCULATE(

    SUM(Invoices[Amount]]),

    DIM_Vendor[Cost category] = "Maintenance")

VAR PE_vehicles_count =

CALCULATE(

    DISTINCTCOUNT(MAN_vehicles[Registration_ID]),

    FILTER(

        ALLSELECTED(MAN_vehicles),

        MAN_vehicles[company] = "PE"))

RETURN

maintenance_sum

/

PE_vehicles_count

Thanks in advance!

[v-kpoloju-msft]

Hi @Donna123,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @FarhanJeelani, @giuliod, @Deku, for your inputs on this issue.

Thanks for the follow-up. You're right to want cost_maintenance to return the correct result even when the company dimension is not in the visual. The current solutions based on SELECTEDVALUE(MAN_vehicles[company]) may break or return unexpected results when company is not explicitly shown in the visual (e.g., in total rows or KPI cards).

To fix this, you should apply the company = "PE" filter directly inside the measure, like this:

cost_maintenance =

VAR maintenance_sum =

    CALCULATE(

        SUM(Invoices[Amount]),

        DIM_Vendor[Cost category] = "Maintenance",

        MAN_vehicles[company] = "PE"

    )

VAR PE_vehicles_count =

    CALCULATE(

        DISTINCTCOUNT(MAN_vehicles[Registration_ID]),

        MAN_vehicles[company] = "PE"

    )

RETURN

DIVIDE(maintenance_sum, PE_vehicles_count, 0)

This ensures the measure always evaluates only for "PE" vehicles, even if the company field is not in the visual.

You can then update total_cost_t simply like this:

total_cost_t =

VAR fuel_cost = SUM(Invoices[cost_petrol_diesel])

VAR maintenance_cost = [cost_maintenance]

RETURN

fuel_cost + maintenance_cost

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @Donna123,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @Donna123,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-kpoloju-msft]

Hi @Donna123,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[FarhanJeelani]

Hi @Donna123 ,

You can modify your DAX formula to ensure that cost_maintenance is calculated only for "PE" and set to 0 for "AB" and other companies. You also need to adjust cost_total_t accordingly.

Step 1: Modify cost_maintenance Calculation
Update your measure to include a condition that only calculates it for "PE" and returns 0 for others.

DAX

cost_maintenance =
VAR maintenance_sum =
CALCULATE(
SUM(Invoices[Amount]),
DIM_Vendor[Cost category] = "Maintenance"
)

VAR PE_vehicles_count =
CALCULATE(
DISTINCTCOUNT(MAN_vehicles[Registration_ID]),
MAN_vehicles[company] = "PE"
)

RETURN
IF(
SELECTEDVALUE(MAN_vehicles[company]) = "PE",
DIVIDE(maintenance_sum, PE_vehicles_count, 0),
0
)

Step 2: Modify cost_total_t to Exclude cost_maintenance for "AB"
Since cost_total_t = cost_maintenance + cost_petrol_diesel, update it as follows:

DAX

cost_total_t =
VAR maintenance_cost =
[cost_maintenance] -- Uses the updated cost_maintenance measure

VAR fuel_cost =
SUM(Invoices[cost_petrol_diesel])

RETURN
fuel_cost +
IF(SELECTEDVALUE(MAN_vehicles[company]) = "PE", maintenance_cost, 0)

Expected Outcome:
cost_maintenance will be calculated only for "PE".
cost_maintenance will be 0 for "AB" and others.
cost_total_t will exclude cost_maintenance for "AB" and other companies.

Please mark this post as solution if it helps you. Appreciate Kudos.

[Donna123]

thanks for your help!

In the final visual and analysis I need to make this measure work also when I don't have "company" dimension in the visual, how to make it happen?

[giuliod]

Hi,

you can try this for the cost_maintenance:

cost_maintenance =
VAR maintenance_sum =
    CALCULATE(
        SUM(Invoices[Amount]),
        DIM_Vendor[Cost category] = "Maintenance"
    )

VAR PE_vehicles_count =
    CALCULATE(
        DISTINCTCOUNT(MAN_vehicles[Registration_ID]),
        MAN_vehicles[company] = "PE"
    )

RETURN 
IF(
    SELECTEDVALUE(MAN_vehicles[company]) = "PE",
    DIVIDE(maintenance_sum, PE_vehicles_count, 0), 
    0
)

 To exclude it from total_cost_t,

total_cost_t =
VAR base_total = SUM(Invoices[Amount]) 

VAR adjustment =
    IF(
        SELECTEDVALUE(MAN_vehicles[company]) <> "PE",
        cost_maintenance,
        0
    )

RETURN
base_total - adjustment

base_total is the sum up all invoices amount that we adjust by cost_maintenance for the company <> PE

[Deku]

cost_maintenance =

VAR maintenance_sum =

CALCULATE(

    SUM(Invoices[Amount]]),

    DIM_Vendor[Cost category] = "Maintenance"

)

VAR PE_vehicles_count =

CALCULATE(

DISTINCTCOUNT(MAN_vehicles[Registration_ID]),

KEEPFILTERS( vehicles[company] = "PE")

RETURN

DIVIDE(

maintenance_sum,

PE_vehicles_count

)