PowerBi sum of column grouped by other column

[Tinus1905]

Hi,

I have a total of a measure and want to sum this by an other column. 

I have the following table. 

ID       Status           Type          Name   Value

123	accept	yes	yellow	12
123	accept	yes		10
456	not possible	standard	green	7
456	not possible	standard		3
789	not possible	standard	green	22
135	accept	no		8

With the following formula I get the total of rows = 4

Count =

    CALCULATE(

    DISTINCTCOUNT(tableA[ID]),

    (tableA[Type] = "yes" || tableA[Type] = "no") ||

    (tableA[Status] = "not possible" &&

    tableA[Name] = "green"),

    REMOVEFILTERS(tableA[filter]))

ID       Status            Type         Name   Value

123	accept	yes	yellow	12
456	not possible	standard	green	7
789	not possible	standard	green	22
135	accept	no		8

Now I want to sum the Value column. Outcome must be: 49

What dax calculation should I use to get it in 1 formula. 

[Tinus1905]

@MFelix @shafiz_p ok, thanks. It seems that I have a little trouble with distinctcount. 

WhenI calculate and I apply filter 1, filter 2 etc, it is possible that I have duplicates. How can I use the filters and then remove the duplicates? 

[MFelix]

Whath is the number you want to count and the total sum amount you want to have? 

Is it the one that you share has a result table:

[Tinus1905]

Sorry I made a mistake. The value column isnt relevant anymore. 

All I have to do is use the measure and multiply by 34. 

So I want to use a new measure with first use the filters and then distinct.  

This is what I have (its working fine), but I need to distinct the ID column.

Count =

    CALCULATE(

    COUNTROWS([tableA[ID]),

    tableA[Type] = IN {"yes", "no"} ||

    tableA[Status] = "not possible" && tableA[Name] = "green",

    REMOVEFILTERS(tableA[filter]))

How can I distinct after the filters has been set. 

[Anonymous]

Hi @Tinus1905 

Actually I hcan not understand the explanation of the latest post completely.
IF possible, please provide more explanation of the details and please provide the your expected outcome.

Best Regards

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

[shafiz_p]

@Tinus1905According to your filter condition (tableA[Type] = "yes" || tableA[Type] = "no"), Both 123 id should come into play but distinct count eleminate one of them. Why it consider 12, why not it considering 10. 10 has equal chance as 12.

[MFelix]

Hi @Tinus1905 ,

For this you need to use SUMX something similar to this:

SUM =
    SUMX ( FILTER(tableA,
    (tableA[Type] = "yes" || tableA[Type] = "no") ||
    (tableA[Status] = "not possible" &&
    tableA[Name] = "green"),
), tableA[Value])

[Tinus1905]

The outcome = BLANK. And I dont see where the column ID is beeing grouped or distinctcount? 

[MFelix]

Hi @Tinus1905 ,

In my mockup it's returning the value altough be aware that since your filter context includes yes and no the total is 51 because it has 10 and 12 for id 123

In this case you do not need the distinct since you are calculating the values of the sum and not the ID per each one you are at a row level filter context.