Forum Discussion
Countif with multiple criteria
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/co1Then 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
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
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?
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/co1Then 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