Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have a table that could look like this:
company name | date | product type | sales |
company1 | 2023-04-01 | apples | 20 |
company1 | 2023-04-01 | pears | 6 |
company1 | 2023-04-02 | pears | 2 |
company1 | 2023-04-02 | apples | 12 |
company1 | 2023-04-03 | apples | 22 |
I want to display it like this:
company name | apples | pears |
company1 | 54 | 8 |
There will be one count per product per date and company. The data will also be controlled by a slicer that allows you to select date range. There is a graph, but total sum per range and company is also desired to be seen under the graph.
The data is loaded via Direct Query, I cannot accomplish this using Pivot column feature. But measures allow me to do a sum or an average for a data source. I can do this for example:
AverageSales = AVERAGE(my_data_source[sales])
But I would like to do instead something like:
apples_average = AVERAGE(my_data_source[sales] IF my_data_source[product type] == "apples")
In my case, the number of product types is finite, low and guaranteed not to change, so making several measures like this manually would solve my problem.
Is it possible to make a measure that only applies on a subset of rows in the data?
Solved! Go to Solution.
@MXXIV try it
measure 1 = CALCULATE( SUM('table'[sales]), 'table'[product type] <> "pears")
measure 2= CALCULATE( SUM('table'[sales]), 'table'[product type] = "pears")
@MXXIV try it
measure 1 = CALCULATE( SUM('table'[sales]), 'table'[product type] <> "pears")
measure 2= CALCULATE( SUM('table'[sales]), 'table'[product type] = "pears")
Thank you very much, this worked even in complicated tables.
@MXXIV Use a Matrix visual. Put Company on Rows and Product on Columns and a simple sum or average.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
109 | |
97 | |
95 | |
38 | |
36 |
User | Count |
---|---|
151 | |
125 | |
75 | |
74 | |
53 |