Filtering values from a single country based on some conditions from a measure

[samioberoi]

Hi,

Is it possible to filter the sum of amount column values from just one table for a single country based on some conditions for each aggregated sum value? For example, i just want my measure to give me just the aggreagted sum values for the country UK only based on its selected conditions as showing below. I want it to be hard coded for the country UK with the conditions showing below. 

COUNTRY	AMOUNT	CONDITION C	CONDITION P	CONDITION 4
UK	24	C1	T13	BLUE
UK	50	C1	T19	GREEN
UK	88	C2	T12	BLUE
USA	16	C1	T11	BLUE
USA	71	C2	T13	GREEN
UK	93	C3	T12	RED
UK	212	C1	T13	BLUE
USA	595	C4	T19	BLUE
UK	68	C3	T12	RED
USA	70	C3	T11	BLUE

Results
UK [C1+T13+BLUE] = 236
UK [C3+T12+RED].  = 161


Thank you

[Anonymous · Accepted solution]

Thanks for the reply from speedramps , danextian  and Ashish_Mathur , please allow me to provide another insight: 
Hi  @samioberoi ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _UK_Value1=
SUMX(
    FILTER(ALL('Table'),
    [COUNTRY]="UK" && [CONDITION]="C1"&&[CCONDITION]="T13"&&[PCONDITION 4]="BLUE"),[AMOUNT])
var _UK_Value2=
SUMX(
    FILTER(ALL('Table'),
    [COUNTRY]="UK" && [CONDITION]="C3"&&[CCONDITION]="T12"&&[PCONDITION 4]="Red"),[AMOUNT])
return
"UK [C1+T13+BLUE] = "&""&_UK_Value1
& UNICHAR ( 10 ) &
"UK [C3+T12+RED] = "&""&_UK_Value2

2. Result:

Best Regards,

Liu Yang

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

[samioberoi]

Hi Liu Tang,

Thank you so much for your help. That is exactly what i was looking for.

Thank you again.

[speedramps]

Try this dax measure whihc with gerate a total of 236.

Then create another measure for UK [C3+T12+RED].

Please click the [accept solution] and thumbs up button to show your appreciation. Thank you

UK C1+T13+BLUE = 
CALCULATE(
SUM(yourdata[AMOUNT]),
yourdata[COUNTRY] = "UK" &&
yourdata[CONDITION C] = "C1" &&
yourdata[CONDITION P] = "T13" &&
yourdata[CONDITION 4] = "BLUE"
)

[danextian]

Hi @samioberoi 

Try something like these:

C1+T13+BLUE = 
CALCULATE (
    SUM ( 'Table'[AMOUNT] ),
    'Table'[COUNTRY] = "UK",
    'Table'[CONDITION C] = "C1",
    'Table'[CONDITION P] = "T13",
    'Table'[CONDITION 4] = "Blue"
)

C3+T12+RED = 
CALCULATE (
    SUM ( 'Table'[AMOUNT] ),
    'Table'[COUNTRY] = "UK",
    'Table'[CONDITION C] = "C3",
    'Table'[CONDITION P] = "T12",
    'Table'[CONDITION 4] = "Red"
)

[samioberoi]

Hi Danextian,

Is it not possible to create in a single measure? Sorry, i forgot to mention before. The one i tried to create before is below, but it doesn't work.

Measure=

 VAR UK [C1+T13+BLUE] = CALCULATE(
                                              SUM(Table[Amount]),

                                              Table[Country] = "UK",
                                               Table[CONDITION C] = "C1",
                                               Table[CONDITION P] = "T13",
                                                Table[CONDITION 4] = "BLUE"

                                               )
VAR[C3+T12+RED].       = CALCULATE( SUM(Table[Amount]),

                                              Table[Country] = "UK",
                                               Table[CONDITION C] = "C3",
                                               Table[CONDITION P] = "T12",
                                               Table[CONDITION 4] = "RED"

                                           )
RETURN
SWITCH(
TRUE(),
UK [C1+T13+BLUE]  <> 0,   UK [C1+T13+BLUE],
UK [C3+T12+RED] <> 0,     UK [C3+T12+RED],
0 )

Thanks

[danextian]

Hi @samioberoi 

The expression after RETURN doesn't make sense to me. What is your criteria/logic for switching to different calculations? What needs to be satisified?

[samioberoi]

Hi, 

The logic is that just a single measure should sum the values from amount column for the UK based on the conditions given in two variables without needing any other filters/slicers to be used. If i use the two conditions separately without putting both in two variables in a single measure it works fine and gives me for UK [C3+T12+RED].  = 161 and for UK [C1+T13+BLUE] = 236, but if i use it in a single measure to try to ask it to filter and separate the two conditional logics using one single measure e.g using the DAX in the post before  from a single measure it should give me both UK [C3+T12+RED].  = 161 and for UK [C1+T13+BLUE] = 236, but it gives only one sum value as e.g 161.
Hope i could explain it better and sorry if i couldn't.
Thanks

[Ashish_Mathur]

Hi,

Your is already a single measure.  What is the problem?

[samioberoi]

Hi Ashish,

Yes, but gives me only one aggregated value but doesn't give me two. For example, it is giving me only UK [C3+T12+RED].  = 161 and doesn't give me UK [C1+T13+BLUE] = 236 as well. I don't know where the problem is.


Thanks