Incorrect value in measure when 2 or more filters are selected

[Anonymous]

I have 3 calculations:

1. Numerator
2. Denominator
3. Percent (By dividing 1 and 2)

For the numerator, it displays the correct value (spend amount based on the combination of selected slicers and selected category/axis on the graph, see image below [numerator should be equal to spend amount in card]).

Amount with sleected values in slicer and graph

For the denominator, the value it should return is the sum of amount by selected slicer (could be multiple slicer (denominator should be equal to spend amount of selected slicers (release date and/or purchase type)), regardless of what is selected in the bar graph category(axis. )see image:
Amount with Slicer

When I select a graph the denominator displays the wrong value (denominator should remain 275BN), resulting to wrong percentage. See image:

Wrong Value when graph is selected

The correct value for denominator only appears if I only choose one of the slicer (either purchase type or release date). It doesnt work if I choose both.

Here's the formula I used for denominator:

Denominator =

CALCULATE(SUM('Spend Analysis'[PO_Amount]),

ALL('Spend Analysis'[Material Group]),

ALLSELECTED('Spend Analysis'[Date]))

I also tried this and nothing happened:

Denominator =

CALCULATE(SUM('Spend Analysis'[PO_Amount]),

ALL('Spend Analysis'[Material Group]),

ALLSELECTED('Spend Analysis'[Date]),

ALLSELECTED('Spend Analysis'[Purchase_Type])

[Ashish_Mathur]

Hi,

Try this

=CALCULATE(SUM('Spend Analysis'[PO_Amount]),ALL('Spend Analysis'[Material Group]))

[Anonymous]

still not working,

[Ashish_Mathur]

There seems to be some issue with the relationships.

[Anonymous]

i'm only using one table so no relationship was made on my date. I tried everything but nothing work for the function ALL or ALLSELECTED

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

Here's the link:
Would really appreciate the help

https://1drv.ms/u/s!Ah4rAyDWhMzAtlWGVcOQKM-LJVNR?e=TzSvgf

[Ashish_Mathur]

I am just way too confused.  Just too many visuals there.  I do not know in which visual, what result do you want to see.

[Anonymous]

Hi,

Please see updated file:

https://1drv.ms/u/s!Ah4rAyDWhMzAtlZAeP-dhqgQG70L?e=DW2WvW

Thanks 🙂

[Ashish_Mathur]

Hi,

This one works

Percent Total Spend denominator = 
CALCULATE(SUM('Spend Analysis'[PO_Amount]),ALL('Spend Analysis'[Material_Description]))

When i select a bar in the Material Description graph, the figure for this measure does not change.

[Anonymous]

I used the material description as the sample bar graph filter.

[Anonymous]

Hi,

Im reattaching the power bi file

please see link: https://1drv.ms/u/s!Ah4rAyDWhMzAtlWGVcOQKM-LJVNR?e=Am66Oz

the denominator still changes when: slicer, release date, and bar graph was selected.

[Ashish_Mathur]

Hi,

Use the ALL similarly for all columns which should not impact the calculation.

[Anonymous]

i tried that, still don’t work

[Anonymous]

Hi, please see attach image. Please use percent share (not overall percent share) beside spend amount.

Percent Share = Numerator / Denominator *100 (as seen in the image, numerator card and denominator card).

Material group is the one that im using as a filter (any bar graph below the cards)

[Mariusz]

Hi @Anonymous 

Try the below.

CALCULATE(
    SUM( 'Spend Analysis'[PO_Amount] ),
    ALLSELECTED()
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

Hi, this didn't help. It only mimicked the behavior of the numerator.

[Mariusz]

Hi @Anonymous 

How about this.

Denominator =
CALCULATE(
    SUM( 'Spend Analysis'[PO_Amount] ),
    ALL( 'Spend Analysis' ),
    VALUES( 'Spend Analysis'[Date] ),
    VALUES( 'Spend Analysis'[Purchase_Type] )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

it still didn't work once i filtered through bar graph