Dax measure- sum of percent of total by group with condition

[mjsteele12]

For simplicity sake, I have the following dummy data:

id  val
1   5
1   30
1   50
1   15
2   120
2   60
2   10
2   10

My desired output is the following:

id  SUM_GT_10%
1   95%
2   90%

SUM_GT_10% can be obtained by the following steps:

1. Calculate the sum of val for each id
2. Divide val by 1
3. sum of 2 if 2 > 10%

using the example data, the sum of val is 100 for id 1 and 200 for id 2, so we would obtain the following additional columns:

id  val   1      2   
1   5     100    5%
1   30    100    30%
1   50    100    50% 
1   15    100    15%
2   120   200    60%
2   60    200    30%
2   10    200    5%
2   10    200    5%

And our final output (step 3) would be sum of 2 where 2> 10%:

id   SUM_GT_10%
1    95%
2    90%

I don't care about the intermediate columns, just the final output, of course.

[Fowmy · Accepted solution]

@mjsteele12 

You can try this measure:

SUM 10% = 

VAR _TOT = 
    CALCULATE(
        SUM('Table'[VAL]),
        ALLEXCEPT('Table','Table'[ID])
)

VAR _VAL = 

SUMX(
FILTER(
ADDCOLUMNS(
    'Table',
    "VAL%", 
    DIVIDE(
        ('Table'[VAL]),
        _TOT
    )
),
[VAL%] > 0.1), 'Table'[VAL])
RETURN
DIVIDE(
    _VAL,
    _TOT
 )

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[mjsteele12]

This worked wonderfully, I can't thank you enough. Dax is proving challenging to learn, coming from other languages..

[Fowmy]

@mjsteele12 

Appreciate it!

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn 

[mjsteele12]

Not sure if you are on s/o but if you want to get the reputation, you can add your answer here:

https://stackoverflow.com/questions/63173853/dax-measure-sum-of-percent-of-total-by-group-with-condi...

[Fowmy]

@mjsteele12 

Thanks, 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn