topic Re: Trendline of the values selected and also of the totals in DAX Commands and Tips

Trendline of the values selected and also of the totals

Anonymous — Mon, 29 Jan 2024 13:30:04 GMT

I am having a slicer with values "A" and "B". I am trying to create a monthwise line chart where if A is selected I want the total_sales of A and overall sales (sum of sales of A and B) as two different lines. If B is selected I want the total_sales of B and overall sales (sum of sales of A and B) as two different lines. If nothing is selected, i want total_sales of A, total_sales of B and overall sales (Total 3 lines). I am having the following measure which calculate total_sales

var _A= CALCULATE([MTD Total Production], ALL('dim date'), DATESINPERIOD('last Date'[Date],MAX('dim date'[Date]),-6,MONTH), USERELATIONSHIP('dim date'[Key],'last Date'[Key]))

for ref: This is the graph for total_sales of A and B when nothing is selected. There is no overall sales line in the graph.

Re: Trendline of the values selected and also of the totals

123abc — Mon, 29 Jan 2024 14:15:29 GMT

Based on your description, it seems like you're trying to create a Power BI measure that calculates total sales based on the selection from a slicer. Below is a measure that you can use to achieve this:

Total Sales =
VAR SelectedValue =
SELECTEDVALUE('YourSlicer'[Value])
RETURN
SWITCH(
TRUE(),
ISINSCOPE('YourSlicer'[Value]),
CALCULATE(
[MTD Total Production],
ALL('dim date'),
DATESINPERIOD('last Date'[Date], MAX('dim date'[Date]), -6, MONTH),
USERELATIONSHIP('dim date'[Key], 'last Date'[Key]),
'YourTable'[Value] = SelectedValue
),
SELECTEDVALUE('YourSlicer'[Value]) = BLANK(),
CALCULATE(
[MTD Total Production],
ALL('dim date'),
DATESINPERIOD('last Date'[Date], MAX('dim date'[Date]), -6, MONTH),
USERELATIONSHIP('dim date'[Key], 'last Date'[Key])
),
CALCULATE(
[MTD Total Production],
ALL('dim date'),
DATESINPERIOD('last Date'[Date], MAX('dim date'[Date]), -6, MONTH),
USERELATIONSHIP('dim date'[Key], 'last Date'[Key])
)
)

Replace 'YourSlicer', 'dim date', 'last Date', and 'YourTable' with your actual table names. This measure calculates total sales based on the selection from the slicer.

In your line chart, you can use this measure as the value and use the slicer selection to control what gets displayed in the chart. You'll need to add appropriate visuals and filters based on your data model and requirements. This measure should help you achieve the desired functionality where you can dynamically show total sales of A, total sales of B, and overall sales based on slicer selections.

Re: Trendline of the values selected and also of the totals

Anonymous — Mon, 29 Jan 2024 18:17:31 GMT

Hi 123abc ,
Thanks for the response. But I need total_sales of A and overall sales (sales of A+B) when A is selected (2 trendlines when A is selected) and total_sales of B and overall sales (sales of A+B) when B is selected (2 trendlines when B is selected). Moreover, the DAX you provided only gives overall sales when nothing is selected, but I want the trendline of A, B and overall sales when nothing is selected.

Re: Trendline of the values selected and also of the totals

123abc — Tue, 30 Jan 2024 05:16:13 GMT

Thank you for the clarification. To achieve the desired behavior, you can adjust the measures as follows:

Total Sales of A =
CALCULATE(
[MTD Total Production],
'YourTable'[Value] = "A"
)

Total Sales of B =
CALCULATE(
[MTD Total Production],
'YourTable'[Value] = "B"
)

Overall Sales =
CALCULATE(
[MTD Total Production],
ALL('YourTable')
)

Overall Sales of A =
CALCULATE(
[MTD Total Production],
'YourTable'[Value] IN { "A", "B" }
)

Now, when nothing is selected, Overall Sales will represent the sales of both A and B, while Overall Sales of A will represent the sales of A+B, which is the same as Overall Sales.

For the line chart:

When "A" is selected in the slicer, you'll display Total Sales of A and Overall Sales of A.
When "B" is selected, you'll display Total Sales of B and Overall Sales.
When nothing is selected, you'll display Total Sales of A, Total Sales of B, and Overall Sales.

Adjust your slicer to choose the appropriate measures based on the selection. This setup should give you the desired trendlines for A, B, and overall sales based on the slicer selection.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Trendline of the values selected and also of the totals

talespin — Tue, 30 Jan 2024 05:17:19 GMT

Hi Anonymous ,

I used Adventureworks database to simulate your use case. I have InternetSales and ProductCategory(kept only 2 products).

Create three measures, for total, A and B. In my case I have created for Total, Bikes and Accessories. Created a slicer for ProductCategory. I have not used any date filtering, you can add it to CALCULATE. Add all three measures to line char y-axis.

MTotalSales =

CALCULATE([MSalesAmount], ALL(DimProductCategory[EnglishProductCategoryName]))

MSalesAmountBikes =

VAR _checkFilter = HASONEFILTER(DimProductCategory[EnglishProductCategoryName])

VAR _selectedValue = SELECTEDVALUE(DimProductCategory[EnglishProductCategoryName])

RETURN

IF( (_checkFilter && _selectedValue = "Bikes") || NOT(_checkFilter),

CALCULATE(SUM(FactInternetSales[SalesAmount]) , ALL(DimProductCategory[EnglishProductCategoryName]), DimProductCategory[EnglishProductCategoryName] = "Bikes"),

BLANK()

)

MSalesAccessories =

VAR _checkFilter = HASONEFILTER(DimProductCategory[EnglishProductCategoryName])

VAR _selectedValue = SELECTEDVALUE(DimProductCategory[EnglishProductCategoryName])

RETURN

IF( (_checkFilter && _selectedValue = "Accessories") || NOT(_checkFilter),

CALCULATE(SUM(FactInternetSales[SalesAmount]) , ALL(DimProductCategory[EnglishProductCategoryName]), DimProductCategory[EnglishProductCategoryName] = "Accessories"),

BLANK()

)

If this doesn't solve your problem, please share pbix file with sensitive data removed or mocked up data.

Re: Trendline of the values selected and also of the totals

Anonymous — Tue, 30 Jan 2024 07:08:27 GMT

Hi Anonymous

Here comes 2 solutions:
I create a set of sample,

The first one:
Create a new table with

Table 2 = {"A","B","Total"}

Then create a measure:

A&B = VAR _uservalue = SELECTEDVALUE('Table 2'[Value]) //get the value from the table 2 VAR _currentDate = MAX('Table'[date]) RETURN SWITCH( _uservalue, "A", CALCULATE( MAX('Table'[value]), FILTER( ALLSELECTED('Table'), 'Table'[user] = "AA" && 'Table'[date] = _currentDate ) ), "B", CALCULATE( MAX('Table'[value]), FILTER( ALLSELECTED('Table'), 'Table'[user] = "BB" && 'Table'[date] = _currentDate ) ), "Total", CALCULATE( SUM('Table'[value]), FILTER( ALL('Table'), 'Table'[date] = _currentDate ) ) )

Then create a line chart and a slicer:

The result is as follow:

Then the second one:
I create 3 measure:

AAA = VAR _currentDate = MAX('Table'[date]) RETURN CALCULATE( MAX('Table'[value]), FILTER( ALLSELECTED('Table'), 'Table'[user] = "AA" && 'Table'[date] = _currentDate ) )

BBB = VAR _currentDate = MAX('Table'[date]) RETURN CALCULATE( MAX('Table'[value]), FILTER( ALLSELECTED('Table'), 'Table'[user] = "BB" && 'Table'[date] = _currentDate ) )

Total = VAR _currentDate = MAX('Table'[date]) RETURN CALCULATE( SUM('Table'[value]), FILTER( ALL('Table'), 'Table'[date] = _currentDate ) )

Then create a matrix and a slicer:

The result is as follow:

Best Regards,

Zhengdong Xu

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