topic Re: Sum of measure by grouping in DAX Commands and Tips

Sum of measure by grouping

Anonymous — Fri, 21 Apr 2023 13:03:17 GMT

Hi,
i need to get sum of the projects by Complexity as below, Final output i need to get

Final output - Italy > 13 low+5 Medium+5 High = 23
- EMEA >11 low+1 Medium+3 High = 15
Projects is a measure - DISTINCTCOUNT(_Projects_custom[ProjectIdentifier])
ProjectIdentifier- Text Data type( ex- IN000886, IN000965, IN000234...)

May i know how to achieve this? Please help

Country	Complexity	Projects	Final OUTPUT
Italy	Low	13	23
Italy	Medium	5
Italy	High	5
APC	Low	1	1
EMEA	Low	11	15
EMEA	Medium	1
EMEA	High	3
US	Low	1	1
UK	Low	3	9
UK	Medium	3
UK	High	3
CANADA	Low	3	7
CANADA	Medium	1
CANADA	High	3

Re: Sum of measure by grouping

Jihwan_Kim — Fri, 21 Apr 2023 14:07:13 GMT

Hi,

I am not sure how your datamodel looks like, but I tried to create the sample pbix flie like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Final outcome measure: = IF ( SELECTEDVALUE ( Complexity[Complexity] ) = "Low", CALCULATE ( [Project count:], ALL ( Complexity[Complexity], Complexity[Index] ) ) )

Re: Sum of measure by grouping

Anonymous — Fri, 21 Apr 2023 14:21:57 GMT

Thanks for the solution.

But in measure you have mentioned only "Low". High & medium complexity not required to add in measure?

please suggest
Thanks

Re: Sum of measure by grouping

Anonymous — Fri, 21 Apr 2023 15:20:13 GMT

Hi Jihwan_Kim
i have used your measure but it is not giving total sum only "Low" complexity is giving values

Note - in my data all the columns are from one table only.

Thanks

Re: Sum of measure by grouping

Jihwan_Kim — Fri, 21 Apr 2023 20:54:50 GMT

Hi,

Please share your sample pbix file's link.

Re: Sum of measure by grouping

v-zhangti — Tue, 02 May 2023 06:23:45 GMT

Hi, Anonymous

You can try the following methods.

Final OUTPUT = Var _Sum=CALCULATE([Projects],ALLEXCEPT('Table','Table'[Country])) Return IF(MAX('Table'[Complexity])="Low",_Sum,BLANK())

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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