Power BI Count the total count of columns that contain a "2"

0
votes

I was able to find the “2”'s per client with the following formula (Column L).

TotalSimultaneous2 = 
    IF(Data[Column1]=2,1,0)+
    IF(Data[Column2]=2,1,0)+
    IF(Data[Column3]=2,1,0)+
    IF(Data[Column4]=2,1,0)+
    IF(Data[Column5]=2,1,0)+
    IF(Data[Column6]=2,1,0)+
    IF(Data[Column7]=2,1,0)+
    IF(Data[Column8]=2,1,0)+
    IF(Data[Column9]=2,1,0)+
    IF(Data[Column10]=2,1,0)

Now I need help finding the total amount of columns that contain at least one “2” column N. In the example below, it would be 7, and that number is coming from the count of all the columns in green since they have at least one “2”.

I can find the simultaneous one. For example, Client4 has the max amount of “2” at the same time, which is 6, but I am having a hard time adding that one “2” from Column10 from Client10 and showing that the number of columns containing a “2” is 7 instead of 6.

enter image description here

Anything helps. Feel free to ask for further clarification, and I will try my best.

1
powerbipowerquery
FYI, this would be much easier if you reshaped your data by unpivoting the (non-CLIENTS) columns. – Alexis Olson
@Alexis I am not sure if I follow. Would you mind elaborating a bit more? – Pepe
trumpexcel.com/unpivot-data – Alexis Olson

1 Answers

1
votes

You can try this below measure. Here I have added 3 columns but you can add as many as you have-

count_column_with_2 = 
CALCULATE(
    DISTINCTCOUNT(your_table_name[col1]),
    FILTER(your_table_name, your_table_name[col1] = 2)
)
+
CALCULATE(
    DISTINCTCOUNT(your_table_name[col2]),
    FILTER(your_table_name, your_table_name[col2] = 2)
)
+
CALCULATE(
    DISTINCTCOUNT(your_table_name[col3]),
    FILTER(your_table_name, your_table_name[col3] = 2)
)