Countif with multiple criteria

[Leyla]

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 delta between the tables V4 and V3.  Then the countif formula in excel counts all cases by years when this is more than 5 or less than -5 in the table 2: V4 and in the table 3: delta V4-V3. In the final Table 4 I divide the values I got in Table 3 vs Table 2. It so easy to do this in excel but very complicated once you try to replicate in PBI. Please help!

[v-juanli-msft]

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 create calculated columns in the table1

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

per =
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

Finally create a new table

Table = SUMMARIZE(ALL(Table1),Table1[year],Table1[per],"row","% of change")

 

Best Regards

Maggie

[Leyla]

Maggie, 

You are genious! Thank you so much for helping me! It solved the problem half-way through but now I have another dilemma which I explained below. Could you please have a look and help me please to solve it. Also would be fabulous if you could explain how the formula pasted below works:

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

Regarding the table now each row gives the same percentage which is repeated for each row but when I try to present through the graph it gets added and percentage for a year does not show a max of 100% but sums up each row and if there are a thousand rows it shows 1,000%. For example, for year 2020 it should be 47% not 120k%.

Also I want to show every 3rd year on the graph but it currently shows the first, and two middle ones but I want to show 2020, 2023,2026, etc. Also attached is the screen copy that shows which template chart I am using. I would appreciate if you could kindly advise how to solve this problem? 

[v-juanli-msft · Accepted solution]

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]

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]

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