Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
I'm still very new to Power BI and learning as quickly as possible, but this is something I'm struggling to get my head around. I have a measure on a table that gives values ranging from -100 to 100 in increments of 10, for example:
-10 |
30 |
50 |
-80 |
30 |
90 |
10 |
I would like to summarise the number of times each measure occurs in a table like so:
-100 | |
-90 | |
-80 | 1 |
-70 | |
-60 | |
-50 | |
-40 | |
-30 | |
-20 | |
-10 | 1 |
0 | |
10 | 1 |
20 | |
30 | 2 |
40 | |
50 | 1 |
60 | |
70 | |
80 | |
90 | 1 |
100 |
I'm not too sure how to achieve this, and really struggling to find similar solutions.
Solved! Go to Solution.
First create a numeric parameter to store the possible values of the measure, ranging from -100 to 100 with an increment of 10. Untick the option to add a slicer to the page, as you won't need it.
Next you need to understand which tables and columns you are summarizing the measure by. For the sake of example I will summarize the Sales table by product category and month.
Create a measure like
Num occurences =
VAR CurrentValue = [Parameter Value]
VAR SummaryTable =
ADDCOLUMNS (
SUMMARIZE ( Sales, 'Date'[Year month], 'Product'[Category] ),
"@num", [Measure you want to count]
)
VAR Result =
COUNTROWS ( FILTER ( SummaryTable, [@num] = CurrentValue ) )
RETURN
Result
Finally, create a visual using the parameter table you created and the new measure.
Perfect thank you, solved what I needed to do!
First create a numeric parameter to store the possible values of the measure, ranging from -100 to 100 with an increment of 10. Untick the option to add a slicer to the page, as you won't need it.
Next you need to understand which tables and columns you are summarizing the measure by. For the sake of example I will summarize the Sales table by product category and month.
Create a measure like
Num occurences =
VAR CurrentValue = [Parameter Value]
VAR SummaryTable =
ADDCOLUMNS (
SUMMARIZE ( Sales, 'Date'[Year month], 'Product'[Category] ),
"@num", [Measure you want to count]
)
VAR Result =
COUNTROWS ( FILTER ( SummaryTable, [@num] = CurrentValue ) )
RETURN
Result
Finally, create a visual using the parameter table you created and the new measure.
User | Count |
---|---|
84 | |
76 | |
74 | |
48 | |
39 |
User | Count |
---|---|
114 | |
56 | |
51 | |
42 | |
42 |