cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SimonChung_GGGG Regular Visitor
Regular Visitor

Measure that condtional

Hi Expert,

 

I would like to have a measure, 

 

Example:

UserCodeRegionValue
AA1UK1
AA1US2
AA2DE3
BA1UK4
BA1US5
BA2DE6
BA2UK7
CA1UK8
CA1US9
CA1DE10
DA2UK11
DA2US12
DA2DE13

 

(conditional that can't create column)

 

Expected measure: [Sum of A2] for whose user have Code "A1", so that result

 

RegionSum of A2
UK7
DE9
total

16

 

 

 

Many thanks,

Simon

7 REPLIES 7
Super User
Super User

Re: Measure that condtional

 Hi @SimonChung_GGGG 

Your result doesn't seem to match what you describe. In any case, try this:

1. Place Region in the rows of a table visual

2. Place Code in a  slicer and select A1

3. Place this measure in the table visual

Measure = SUM( Table1[Value] )

4. You can add more slicers if you want to refine the sum further

 

SimonChung_GGGG Regular Visitor
Regular Visitor

Re: Measure that conditional

@AlB 
Thanks for your reply, however that's not my expected answer. 
I've checked my expected result is corrrect, 
Please see the picture to be clear.

 

Raw table

image.png

 

Expected measure: [Sum of A2] for those user have "A1"

 

image.png

 

Many thanks

Super User
Super User

Re: Measure that conditional

 

Ok, but then you'd need to explain better what you need. You are leaving user D out of the calculation. Is that what you want? If so, you just have to include another slicer for User and select users A, B, C only, leaving D out

Another option would be not to use this User slicer and embed the filtering in the measure

MeasureV2 =
CALCULATE ( SUM ( Table1[Value] ), Table1[User] <> "D" )

The first option is probably more versatile, since you can select the users of interest on the fly

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

 

SimonChung_GGGG Regular Visitor
Regular Visitor

Re: Measure that conditional

@AlB  Thanks so much for your afford.

however it's not my expected answer as well

" You are leaving user D out of the calculation. Is that what you want?" - correct, but this should be done by calculation and not hard coding. Since user D doesn't have code "A1", so D have to be filtered out. 

To add Slicer is not my requirement as well.

Highlighted
Super User
Super User

Re: Measure that conditional

 

1. Place Region in the rows of a table visual

2. Place this measure in the table visual

 

Measure-Sum of A2 =
SUMX (
    Table1;
    IF (
        Table1[Code] = "A2"
            && "A1" IN CALCULATETABLE ( DISTINCT ( Table1[Code] ); ALLEXCEPT ( Table1; Table1[User] ) );
        Table1[Value];
        0
    )
)

See it all at work in the attached file.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

SimonChung_GGGG Regular Visitor
Regular Visitor

Re: Measure that conditional

@AlBMany thanks

I'm wondering that can your code here vary with the slicer changing?

 "A1" IN CALCULATETABLE ( DISTINCT ( Table1[Code] ); ALLEXCEPT ( Table1; Table1[User] ) )

It's expected dynamical

Super User
Super User

Re: Measure that conditional

@SimonChung_GGGG 

I'd need more details about what you exactly need. An example would be of great help

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)