Forum Discussion

Leyla's avatar
Leyla
Regular Visitor
7 years ago
Solved

Countif with multiple criteria

HI, please help me to find out the solution for the following situation: I have 2 tables V3 and V4 and I need to get a third table 4 by rows and % which use the temporary table 3 which is the de...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Leyla

    1.

    table3 = var re=RELATED(Table2[value]) return re-[value]

    this formula equal to table3=RELATED(Table2[value])

    it returns the related value from Table2 in Table1 based on the key column(BASE1<->BASE2)

     

    2.

    create a measure instead of a calculated column(in the previous post, i create a calculated column called "per")

    Measure 2 = var co1= CALCULATE(COUNT([value]),FILTER(ALLEXCEPT(Table1,Table1[year]),[value]<-5||[value]>5)) 
                var col2=CALCULATE(COUNT(Table1[table3]),FILTER(ALLEXCEPT(Table1,Table1[year]),[table3]<-5||[table3]>5)) 
                return col2/co1

    Then change the data format to percentage

     

    3.

    You could change Type(in X-axis) from continous to categorical, but it shows every year on the x-axis. 

    It seems to be that Chart visuals will adjust the data labels automatically (based on label size and chart size) in this scenario. I would suggest you add it as an idea on Power BI Ideas forum to improve Power BI on this feature.

     

    Best Regards

    Maggie

     

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi Leyla

Assume your table is like

      

Table1                                                        Table2

First create a calculated column[BASE1] in table1 and another called [BASE2] in Table2

 

BASE1 = CONCATENATE([category],[year]) 

BASE2 = CONCATENATE([category],[year]) 

Then create a relationship between the two tables

Table1[BASE1] to Table2[BASE2]

 

then i can create a temporary table3 using a measure

Measure = MAX(Table2[value])-MAX(Table1[value])

Add this measure in the "Value" field of the matrix chart

 

How ever, when i calculate the percentage using

conditional count in Table3/conditional count in Table2

for example, for year 2018, 

conditional count in Table3->(-58-89-4567)

conditional count in Table2->65

percentage should be

(-58-89-4567)/65

 

It is not like yours, could you tell me how you calculate it ?

 

Best Regards

Maggie

Leyla's avatar
Leyla
Regular Visitor
7 years ago

Hi v-juanli-msft,

Thank you fro attempting to address my issue. If I oculd I would insert here the excel file which would clearly show the calculus behind the tables but as I do not see the way how to do this, I will try to explain. The B20 cell is calculated as B11-B5, the same applies to B21 which is B12-B6 up to D23 which is D14-D8.

 

 

B26 which is the final result for 2018 year is B17/B2. B17 is his formula =COUNTIF(B20:B23,">5")+COUNTIF(B20:B23,"<-5") and B2 is this formula =COUNTIF(B5:B8,">5")+COUNTIF(B5:B8,"<-5"). Hope it helps, but if not, please kindly let me know. Would be good if you oculd explain how to add here the excel file, so that you would not re-do the whole exercise.

Thanks,

Leyla