Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear experts,
I have table where two columns are matter of interest and try to count filtered value based by columns data.
Time | Country | Population |
1/19 | CAN | Increased |
2/19 | CAN | No Change |
3/19 | CAN | Increased |
1/19 | US | Increased |
I'm interested to know e.g. count of Population Increased in a specific country.
Example: Home many times Popoluation has increased in CAN? Answer is 2
In Excel this can be calulated by using "countifs" function
= countifs($Population:$Population;"Increased";$Country:$Country;"CAN")
Can similar calculation done in PowerBI?
Solved! Go to Solution.
Hi @Anonymous
You can create a measure and place it in a card visual:
Measure = CALCULATE ( COUNT ( Table1[Population] ), Table1[Population] = "Increased", Table1[Country] = "CAN" )
You could also create a simpler and more versatile measure:
Measure 2 = COUNT ( Table1[Population] )
and then use a slicer in Table1[Population] and another one on Table1[Country] to select "Increased" and "CAN" (or any other combination you need)
Try this:
1. Place Table1[Country] and Table1[Population] in a table visual. Make sure both are displayed with the 'Don't summarize' option.
2. Place the measure we created before, [Measure 2], in the table visual. Note we already have Country and Population in the filter context in each row.
3. Create this measure (the 'value to look for') and place it in the table visual:
Measure 3 = VAR _Factor = SWITCH ( SELECTEDVALUE ( Table1[Population] ), "Increased", 100, "Decreased", 50 ) RETURN _Factor * [Measure 2]
Hi @Anonymous
You can create a measure and place it in a card visual:
Measure = CALCULATE ( COUNT ( Table1[Population] ), Table1[Population] = "Increased", Table1[Country] = "CAN" )
You could also create a simpler and more versatile measure:
Measure 2 = COUNT ( Table1[Population] )
and then use a slicer in Table1[Population] and another one on Table1[Country] to select "Increased" and "CAN" (or any other combination you need)
Great, thanks this will do the trick.
Now I need to figure out how to value those better and present at the same table, where increased count get's value 100 and no change value 50
Country | Population | Count | Value to look for | Note |
CAN | Increased | 2 | 200 | "Increased" Count multiplied * 100 |
CAN | No change | 1 | 50 | "No change"Count multiplied * 50 |
US | Increased | 1 | 100 | "Increased" Count multiplied * 100 |
Not sure if that can done at one column.
Try this:
1. Place Table1[Country] and Table1[Population] in a table visual. Make sure both are displayed with the 'Don't summarize' option.
2. Place the measure we created before, [Measure 2], in the table visual. Note we already have Country and Population in the filter context in each row.
3. Create this measure (the 'value to look for') and place it in the table visual:
Measure 3 = VAR _Factor = SWITCH ( SELECTEDVALUE ( Table1[Population] ), "Increased", 100, "Decreased", 50 ) RETURN _Factor * [Measure 2]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |