topic Re: Power BI Dax combine sub total of visible tables in DAX Commands and Tips

Power BI Dax combine sub total of visible tables

msam1977_SM — Fri, 08 Nov 2024 15:20:55 GMT

I'm looking to build a DAX statement based on the visual tables, and I've attached a sample image for reference. I have a toggle button that shows and hides certain tables, but I need a total that only includes the visible tables. Currently, I'm getting the sum from all tables, not just the ones that are unhidden. Could you help me with this? Thank you

Re: Power BI Dax combine sub total of visible tables

VahidDM — Sat, 09 Nov 2024 01:30:11 GMT

Hi msam1977_SM

In Power BI, DAX measures cannot detect the visibility state of visuals controlled by bookmarks or selection panes. However, you can achieve your goal by linking the table visibility to data selections that DAX can reference. Here's how:

Solution:

Create a Table for Toggle Selections:

Create a new table (e.g., TableVisibility) with the names of the tables you want to show or hide:

TableVisibility =

DATATABLE(

"TableName", STRING,

{

{"Table1"},

{"Table2"},

{"Table3"} // Add as many tables as needed

}

)

Add a Slicer for Table Selection:

Insert a slicer using the TableVisibility[TableName] column.
Allow multiple selections so users can select which tables to display.

Modify Each Table Visual:

For each table visual, add a visual-level filter:

Field: TableVisibility[TableName]
Filter Condition: TableName is equal to the name corresponding to that visual.

Example: For "Table1" visual, set the filter to TableName = "Table1".

Create Measures for Each Table Total:

Calculate totals for each table individually.
Example:

Total_Table1 = SUM('Table1'[Value])

Total_Table2 = SUM('Table2'[Value])

Create a Measure for the Combined Total:

Use the slicer selection to sum totals only from visible tables:

TotalVisible =

VAR SelectedTables = VALUES(TableVisibility[TableName])

RETURN

SUMX(

SelectedTables,

SWITCH(

TRUE(),

TableVisibility[TableName] = "Table1", [Total_Table1],

TableVisibility[TableName] = "Table2", [Total_Table2],

TableVisibility[TableName] = "Table3", [Total_Table3],

)

Display the Combined Total:

Add a card or visual to display the TotalVisible measure.
This total will update based on the tables selected in the slicer.

Optional – Hide the Slicer:

If you prefer to use buttons instead of showing the slicer:

Use bookmarks and buttons to set the selection in the slicer.
Assign bookmark actions to buttons to simulate toggling.

Important Notes:

DAX Limitations:

DAX cannot read the visibility state of visuals controlled by bookmarks.
Measures can only respond to data selections, not visual properties.

Consistent Interaction:

By tying the visibility and totals to the same selection (the slicer), you ensure that both the displayed tables and the combined total remain in sync.

User Experience:

You can format the slicer to look like toggle buttons for a cleaner interface.
Hide slicer headers and adjust formatting to match your report design.

Conclusion:

By linking table visibility to slicer selections that your DAX measures can reference, you can dynamically calculate totals based on which tables are visible to the user. This approach aligns the data model with the report visuals, providing the functionality you need.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

LinkedIn|Twitter|Blog |YouTube

Re: Power BI Dax combine sub total of visible tables

Anonymous — Mon, 11 Nov 2024 02:36:56 GMT

Hi msam1977_SM ,

You can use measure to manually calculate the desired result separately and then use bookmark to switch between different visualizations. You can refer to the following example, details can be found in the attachment.

Measure = VAR _sum1 = CALCULATE(SUM(financials[ Sales]),FILTER(ALL(financials),'financials'[Year] = 2013 || 'financials'[Year] = 2014)) VAR _sum2 = CALCULATE(SUM(financials[ Sales]),FILTER(ALL(financials),'financials'[Month Number] = 1 || 'financials'[Month Number] = 2 || 'financials'[Month Number] = 3)) RETURN _sum1 + _sum2

Measure 2 = VAR _sum1 = CALCULATE(SUM(financials[ Sales]),FILTER(ALL(financials),'financials'[Year] = 2013 || 'financials'[Year] = 2014)) VAR _sum2 = CALCULATE(SUM(financials[ Sales]),FILTER(ALL(financials),'financials'[Month Number] = 1 || 'financials'[Month Number] = 2 || 'financials'[Month Number] = 3)) VAR _sum3 = CALCULATE(SUM(financials[ Sales]),FILTER(ALL(financials), 'financials'[Date].[QuarterNo] = 1 || 'financials'[Date].[QuarterNo] = 2)) RETURN _sum1 + _sum2 + _sum3

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Power BI Dax combine sub total of visible tables

Poojara_D12 — Mon, 11 Nov 2024 11:07:24 GMT

Hi msam1977_SM ,

DAX Visible Total = CALCULATE( SUM(Sales[Amount]), FILTER( 'Visibility Control', 'Visibility Control'[IsVisible] = 1 ) )

DAX can't detect visual visibility directly. You need to use slicers, parameters, or bookmarks to control the state and then use DAX to evaluate that state.
The slicer or parameter-based solution allows you to build dynamic measures that change based on user input, such as selecting visible tables in your visual.

If you provide more details about how you're toggling the tables (using bookmarks, buttons, or slicers), I can give more specific advice or DAX formulas tailored to your setup.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant

Re: Power BI Dax combine sub total of visible tables

msam1977_SM — Mon, 11 Nov 2024 20:07:24 GMT

Thank you for the idea! I made some customizations to my Power BI setup. I created three tables, each dedicated to a specific slicer, and connected them to the data to enable filtering. This approach allowed me to remove the bookmark or toggle functionality from the code.

For the "TotalYearly" calculation, I used the following code as a reference, which might be helpful to others:

_TotalValue =
VAR _SumProd_Month =
CALCULATE(
SUM('Calendar Year Rebates'[Rebate]),
'Calendar Year Rebates'[Type] = "Month",
'Calendar Year Rebates'[StageFlag] = "PROD"
)

VAR _SumStage_Month =
CALCULATE(
SUM('Calendar Year Rebates'[Rebate]),
'Calendar Year Rebates'[Type] = "Month",
'Calendar Year Rebates'[StageFlag] = "Stage"
)

VAR _SM_Month = SELECTEDVALUE(Dim_Month[ToggleMonth])

VAR _MonthValue = IF(
HASONEVALUE(Dim_Month[ToggleMonth]),
IF(_SM_Month = "ON", _SumStage_Month, _SumProd_Month),
0
)

VAR _SumProd_Quarter =
CALCULATE(
SUM('Calendar Year Rebates'[Rebate]),
'Calendar Year Rebates'[Type] = "Quarter",
'Calendar Year Rebates'[StageFlag] = "PROD"
)

VAR _SumStage_Quarter =
CALCULATE(
SUM('Calendar Year Rebates'[Rebate]),
'Calendar Year Rebates'[Type] = "Quarter",
'Calendar Year Rebates'[StageFlag] = "Stage"
)

VAR _SM_Quarter = SELECTEDVALUE(Dim_Qtr[ToggleQtr])

VAR _QuarterValue = IF(
HASONEVALUE(Dim_Qtr[ToggleQtr]),
IF(_SM_Quarter = "ON", _SumStage_Quarter, _SumProd_Quarter),
0
)

VAR _SumProd_Year =
CALCULATE(
SUM('Calendar Year Rebates'[Rebate]),
'Calendar Year Rebates'[Type] = "Year",
'Calendar Year Rebates'[StageFlag] = "PROD"
)

VAR _SumStage_Year =
CALCULATE(
SUM('Calendar Year Rebates'[Rebate]),
'Calendar Year Rebates'[Type] = "Year",
'Calendar Year Rebates'[StageFlag] = "Stage"
)

VAR _SM_Year = SELECTEDVALUE(Dim_Year[ToggleYear])

VAR _YearValue = IF(
HASONEVALUE(Dim_Year[ToggleYear]),
IF(_SM_Year = "ON", _SumStage_Year, _SumProd_Year),
0
)

VAR _TotalValue = _MonthValue + _QuarterValue + _YearValue

VAR __ValueToShow = IF(ISBLANK(_TotalValue), "-", _TotalValue)

RETURN
IF([Complex Selector]=1,__ValueToShow,BLANK())

Re: Power BI Dax combine sub total of visible tables

msam1977_SM — Mon, 11 Nov 2024 20:11:17 GMT

Thank you so much for the help!! I was able to resolve the issues. I have posted the solution too.