How to use sumifs in Power BI

[Anonymous]

Hi all,

Can anyone teach me how to use sumifs to calculate the result from the matrix table below?

Matrix table:
Everything is in the data model. I created a matrix table to measure the Actual ($) and Plan ($) cost for F2F and Virtual by using Average calculation.

However, I dont know how to proceed to the next steps.

Results that i am looking for by using sumifs in excel:
sum range: Actual or Plan cost

criteria range (1) : Market
criteria range (2): Delivery channel (OE) 

** The below calculation must be derrived from the matrix table above**

Please let me know how to achieve this result.

Best regards & Thank you.

Kelly.

[Ashish_Mathur]

Hi,

Create a matrix visual and drag Delivery channel to the column labels, Market to the row labels.  Write this measure and drag it to the visual.

Total = sum(Data[plan])

Hope this helps.

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Try this:

Measure =
CALCULATE(
    SUM( 'table'[value] ),
    ALLEXCEPT( 'table', [Market], [Delivery channel], [Actual or Plan cost] )
)

Or, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataVitalizer]

Hi @Anonymous 

As long as you want to achieve that specific result why not to create two matrix visuals and in each one you will use a different sum range and moreover you can add whatever filters in that visual?

Did it work ? 👌Mark it as a solution to help spreading knowledge 👉A kudos would be appreciated