How to show 'All' word as a dimension value in x axis for a bar chart?

[gsandip]

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 ?

[danextian]

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.

[gsandip]

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:

[danextian · Accepted 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

[gsandip]

Hi,

Can anybody help me on that?