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.
Hi,
I have to show product categories in x axis and sum(sales) in Y axis as a measue.
But along with all product categories, I have to show 'All' as a categiry value in x axis, where all product sales will show.
Can it be achieved ?
Solved! Go to Solution.
Hi @gsandip ,
I had to change your sample data a bit so they can be split between 2022 and 2023.
First create a calculated table to get all the distinct values from Product Category then create a UNION with another table that contains ALL.
Category =
VAR __CATEGORY =
ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[Product Category] ), "Sort", 1 )
VAR __ALL =
ROW ( "Product Category", "ALL", "Sort", 0 )
RETURN
UNION ( __CATEGORY, __ALL )
Sort column is added to custom sort Product Category by another column. ALL will be the first in the list. Make sure to custom sort.
Create the measure:
Sales Value by Product Category =
IF (
SELECTEDVALUE ( Category[Product Category] ) = "ALL",
SUM ( 'Table'[Sales Value] ),
CALCULATE (
SUM ( 'Table'[Sales Value] ),
FILTER (
'Table',
'Table'[Product Category] IN VALUES ( Category[Product Category] )
)
)
)
Sample result
Please see sample attached pbix
Proud to be a Super User!
Hi @gsandip ,
It is achievable but not out of the box. You'll be needing a disconnected table that holds all product categories and another row for the word ALL and then a measure to return the value for each category + the value for ALL. Post a sample data so I can provide you with a sample pbix.
Proud to be a Super User!
Hi @danextian,
Below is sample data:
ID | Sales Value | Product Category | Reporting Date |
1 | 12 | Cat1 | 20230731 |
2 | 13 | Cat2 | 20230731 |
3 | 14 | Cat2 | 20230731 |
4 | 16 | Cat1 | 20230630 |
5 | 67 | Cat1 | 20230630 |
Now I need to show 'Product Category' in X Axis and Sales = SUM(Sales Value) measure in Y asix like below:
Hi @gsandip ,
I had to change your sample data a bit so they can be split between 2022 and 2023.
First create a calculated table to get all the distinct values from Product Category then create a UNION with another table that contains ALL.
Category =
VAR __CATEGORY =
ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[Product Category] ), "Sort", 1 )
VAR __ALL =
ROW ( "Product Category", "ALL", "Sort", 0 )
RETURN
UNION ( __CATEGORY, __ALL )
Sort column is added to custom sort Product Category by another column. ALL will be the first in the list. Make sure to custom sort.
Create the measure:
Sales Value by Product Category =
IF (
SELECTEDVALUE ( Category[Product Category] ) = "ALL",
SUM ( 'Table'[Sales Value] ),
CALCULATE (
SUM ( 'Table'[Sales Value] ),
FILTER (
'Table',
'Table'[Product Category] IN VALUES ( Category[Product Category] )
)
)
)
Sample result
Please see sample attached pbix
Proud to be a Super User!
Hi,
Can anybody help me on that?
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 |
---|---|
96 | |
95 | |
82 | |
71 | |
64 |
User | Count |
---|---|
116 | |
106 | |
94 | |
79 | |
72 |