DAX - Slicer option to show aggregated results

[Anonymous]

Hello everyone,

Is there a way I can do something like the below:

You have a slicer selection which adds columns to the report as you do multiple selections (this part I know how to do....very EASY).

But now, what if the user wants to see a consolidated/aggregated/summarized view for all the BUs !!!

They don't want to see all the columns added but only the total for everything.

Is this possible ?

[v-jiascu-msft]

Hi @Anonymous ,

Obviously, the "All" doesn't exist. We need to create a new table to include this value. Please refer to my test below. Your formula could be this one.

BUs =
UNION ( VALUES ( 'table'[BU] ), { "All" } )

Then create a measure.

Measure 11 =
IF (
    MIN ( BUs[BU] ) = "All",
    CALCULATE ( SUM ( Sales[SalesQuantity] ), ALL ( BUs ) ),
    SUM ( Sales[SalesQuantity] )
)

Best Regards,

[Anonymous]

Hello @v-jiascu-msft,

Where do you put the new measure? In the new unionized table ?

Thanks,

Jason

[v-jiascu-msft]

Hello Jason,

It's a Matrix visual. You can put the new measure in the Values field. You can create it on your side based on the snapshot in my last post, which shows the column of each field of the Matrix.

Best Regards,

[Anonymous]

@v-jiascu-msft@v-jiascu-msft  It seems to be working but now I am having another issue and it is driving me insane....

I have created my new slicer using the UNION fonction + "All".

Everything is good.

Except:

I have a Daily% measure that is working for every single selection including our famous "All".

Also have a rolling average measure that is showing data for every selection except "All"

I do not know why this is happening and it is really frustrating.

 Here is a schematics so that you can better understand my predicament:

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Because the "ALL" has no related values, we can't use a formula like that. Maybe you should rewrite it like the other two measures.

Avg_OT%_GL_Cond =
    if(min(GL_ALL[GL]) = "All",
        calculate(..., all(GL_ALL)), 
        averagex(...)
     )

Best Regards,

[Anonymous]

It became too complex so I just did a "brute force" thing.

Referenced the query, summed up and appended back to the original.