Measure to remove one filter but add another

[Rena]

I have a PBI with multiple data tables.  I am trying to do a simple basically sumif but can't seem to make it work.

I have 10 plants that make different products.

3 of those plants all make product ABC.

I have a slicer that lets you pick product ABC from a materal table.

Once that is selected, it tells you how much total volume the entire company (all 10 plants) made of product ABC.

That comes from the volume table and is simply = sum ('volume table'[actual])

What I am stuck on, is how do I make a second measure that says what the total volume for those 3 plants only are?

I have CALCULATE (SUM('volume table'[actual]), ALL('material map'[brand]))

This gives me the total volume for all 10 plants but doesn't filter it to just the 3 that have volume of product ABC.  

What do I need to add so that it sums the volume for all brands but only if the actual volume for the selected brand is greather than 0?

[TheoC · Accepted solution]

Hi @Rena 

I hope you're doing well!

I created some dummy data based on my understanding of your post.  But please let me know if the below is what you are after.  Just note that where no selection has been made in the slicer, I have just added "Choose From Slicer" to alert the user to select an option from the slicer. Otherwise, this can be adjusted to whatever you'd like:

Once a user selects from the slicer, the results change accordingly.  Again, I've just added dummy data on the basis of what I understood. Please let me know if I've misunderstood.

TotalVolumeForSelectedPlants = 

VAR _SelectedBrand = SELECTEDVALUE ( 'Material Table'[Brand] )

VAR _PlantsWithSelectedBrand =

    CALCULATETABLE (
        VALUES ( 'Volume Table'[Plant] ) ,
        'Volume Table'[Brand] = _SelectedBrand ,
        'Volume Table'[Actual] > 0
    )

VAR _result =

CALCULATE (
    [Sum of Actual Volume] ,
    'Volume Table'[Plant] IN _PlantsWithSelectedBrand,
    ALL ( 'Material Table'[Brand] )
)

RETURN

IF ( ISBLANK ( _result ) , "Choose From Slicer" , _result )

Hope this helps!

Theo 🙂

[Rena · Accepted solution]

Got this figured out - leaving it here in case someone else needs it in the future

Brand Volume Test =

VAR SelectedBrand = SELECTEDVALUE('Material Map'[Brand])

VAR TotalBrands =

    TREATAS(

        VALUES('Material Map'[Brand]),

        'Volume Lock'[Brand])

VAR PlantwithBrands =

    CALCULATETABLE(

        VALUES('Volume Lock'[Plant Number]),

        'Volume Lock'[Brand] IN TotalBrands,

        'Volume Lock'[Volume]>0)

RETURN

    CALCULATE(

        SUM('Volume Lock'[Volume]),

        'Volume Lock'[Plant Number] IN PlantwithBrands,

        ALL('Material Map'[Brand]))

[Rena]

I have multiple tables.

One table maps the material used to the brands

One table holds the actual volume produced by brand, material, and plant

One table holds the actual spend by plant

I have 30 total plants.  Each plant produces a different set of brands, but multiple plants produce the same brand.

On my slicer, I am selecting a brand.  When I select that brand, I am getting how much total the company spends to produce that brand based on volume at the plant level.

Example: Brand A is produced in Plants 1, 2, and 3.

Plant 1 spends $10MM and 42% of it's production is Brand A so the calculation [account spend]*[volume %] says $4.2MM

Plant 2 spends $4MM and 1% of its production is Brand A so the calculation says $40k

Plant 3 spends $3MM and 75% of its production is Brand A so the calculation says $2.3MM

The problem is my total is giving me 45% of $17MM for $7.7MM instead of adding the 3 separate measures up which should total $6.5MM.

Currently I use this basic DAX to calculate the [account spend] (volume spend is the same but just looking at volume instead of spend)

VAR TotalBrands =

    TREATAS(

        VALUES('Material Map'[Brand]),

        'Volume Lock'[Brand])

VAR PlantwithBrand =

    CALCULATETABLE(

        VALUES('Volume Lock'[Plant Number]),

        'Volume Lock'[Brand] IN TotalBrands,

        'Volume Lock'[Volume]>0)

VAR Result =

    CALCULATE(

        [Account Spend],

        'Actual Spend'[Plant] in PlantwithBrand)

RETURN

    Result

Any ideas?

[v-jtian-msft]

Hello, HotChilli,thanks for your concern about this issue.
And I would like to share some additional solutions below.

Hi,@Rena .I am glad to help you.

Has your problem been solved?
If you have found suitable solutions, please share them as it will help more users with similar problems.
Or you can mark the valid suggestions provided by other users as solutions.
Thank you very much for your understanding and support of Power BI.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[HotChilli]

Sounds like a DAX totals issue.

I'm not really a fan of these overly-verbal descriptions of problems. There's too much information and too much left unsaid.

If you provide a sample pbix and show the desired result, you'll get a quicker answer.

[Rena]

Worked perfectly.  Thank you so much! 

[TheoC · Accepted solution]

Hi @Rena 

I hope you're doing well!

I created some dummy data based on my understanding of your post.  But please let me know if the below is what you are after.  Just note that where no selection has been made in the slicer, I have just added "Choose From Slicer" to alert the user to select an option from the slicer. Otherwise, this can be adjusted to whatever you'd like:

Once a user selects from the slicer, the results change accordingly.  Again, I've just added dummy data on the basis of what I understood. Please let me know if I've misunderstood.

TotalVolumeForSelectedPlants = 

VAR _SelectedBrand = SELECTEDVALUE ( 'Material Table'[Brand] )

VAR _PlantsWithSelectedBrand =

    CALCULATETABLE (
        VALUES ( 'Volume Table'[Plant] ) ,
        'Volume Table'[Brand] = _SelectedBrand ,
        'Volume Table'[Actual] > 0
    )

VAR _result =

CALCULATE (
    [Sum of Actual Volume] ,
    'Volume Table'[Plant] IN _PlantsWithSelectedBrand,
    ALL ( 'Material Table'[Brand] )
)

RETURN

IF ( ISBLANK ( _result ) , "Choose From Slicer" , _result )

Hope this helps!

Theo 🙂

[Rena]

@TheoC 

So sorry to keep bothering you.  But I put that in and then when I selected all brands I end up with a syntax error saying multiple values where only one value was expected.  My old formula works for multiple brands and this formula only works for single brands.  Is there3 a way to change the return so that if it is multiple brands I can put in my old formula and single brands can leave (Result)?

[TheoC]

Hi @Rena 

I'm so sorry for late reply! Weekend followed by a public holiday here in Australia so only just got back on now.  Did you end up sorting this out? 

If not, you can use "IFERROR ( [Measure 1] , [Measure 2] )" to work around this.  Otherwise, you should be able to update the end of the measure (after where it says RETURN) with "IFERROR ( ...... , [Measure 2])"  The ...... represents whatever the DAX code is after the "RETURN" in the measure itself. 

Please let me know if that makes sense or you need further help!

Theo 🙂

[Rena · Accepted solution]

Got this figured out - leaving it here in case someone else needs it in the future

Brand Volume Test =

VAR SelectedBrand = SELECTEDVALUE('Material Map'[Brand])

VAR TotalBrands =

    TREATAS(

        VALUES('Material Map'[Brand]),

        'Volume Lock'[Brand])

VAR PlantwithBrands =

    CALCULATETABLE(

        VALUES('Volume Lock'[Plant Number]),

        'Volume Lock'[Brand] IN TotalBrands,

        'Volume Lock'[Volume]>0)

RETURN

    CALCULATE(

        SUM('Volume Lock'[Volume]),

        'Volume Lock'[Plant Number] IN PlantwithBrands,

        ALL('Material Map'[Brand]))

[Rena]

@TheoC 

One more help if you don't mind.

Same sheet.  Now I am adding the last part which is pulling in one more table and adding values specifically from that table that already have a measure on them.

New table is Spend by Plant

I am taking each plant and multiplying their spend by the volume of the brand from above.  I have that working fine.  

Example: Plant 1 - Total spend is $10MM with 42% ABC volume for adjusted spend of $4.2MM

                Plant 2 - Total spend is $3MM with 74% ABC volume for adjusted spend of $2.2MM

                Plant 3 - Total spend is $5MM with 1% ABC volume for adjusted spend of $50K

                Total plant spend is $18MM and total volume between all 3 plants is 42% for product ABC

Currently it is adding all of their spend up ($18MM and multiplying that number by 42% total volume) and giving me a $7.5MM value but I need it to add up each plant individually for the $6.4MM.

No matter what I write I am totaling the volume and the spend and dividing rather than adding the already divided totals.

[TheoC]

Hi @Rena 

Sorry for late reply. Just wanting to confirm you figured the second question out?

If not, let me know and I can assist.

Thanks heaps,

Theo

[Rena]

@TheoC 

No.  I am still stuck on trying to get the plant costs to mutiply by the volume individually and then add up.  I can only get it to multiply the total plant costs by the volume %.  

Here is what I am currently using

[TheoC · Accepted solution]

Hi @Rena 

Sorry it took so long. Required a little extra thought and then the penny dropped... a lot of the challenge was arising from using Material Table "Brand" as the Slicer given that all of the measures were reflecting the Volume Table "Brand".   I was running into the same issues until this point.

I am hoping that the attached is what you are after. I have started from a blank canvas in the attached so please let me know how it all goes!  

Below is an output:

Hope this helps!

Theo 🙂

[Rena]

I had to throw a sumx() around it, and massage it a bit since I couldn't switch my filter to volume due to other tables on the same page needing the material map, but changing the formula around to get rid of the calculatetable worked like a charm!

Thank you so much for your help!  I had spent days on this!!

[TheoC]

You're a legend, @Rena!  Really glad it all came together! 

All the best!

Theo 🙂