Trendline of the values selected and also of the totals

[Anonymous]

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. 

[Anonymous]

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.

[talespin · Accepted solution]

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.

[123abc]

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.

[Anonymous]

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.

[123abc]

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.