topic Re: Dax measure- sum of percent of total by group with condition in DAX Commands and Tips

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

mjsteele12 — Thu, 30 Jul 2020 13:27:46 GMT

For simplicity sake, I have the following dummy data:

My desired output is the following:

id  SUM_GT_10%
1   95%
2   90%

SUM_GT_10% can be obtained by the following steps:

Calculate the sum of val for each id
Divide val by 1
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.

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

Fowmy — Thu, 30 Jul 2020 14:03:07 GMT

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

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

mjsteele12 — Thu, 30 Jul 2020 14:41:23 GMT

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

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

Fowmy — Thu, 30 Jul 2020 14:45:53 GMT

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

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

mjsteele12 — Thu, 30 Jul 2020 14:48:55 GMT

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-condition

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

Fowmy — Thu, 30 Jul 2020 15:08:59 GMT

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