Creating a line chart with a dynamic total line

[pcrawfor]

Hello,

I'm creating a line chart that graphs the total amount of downloads by region over a period of time. The X-axis is the date, formated like MMM-YYYY. The Y-axis is the number of downloads. Each line on the graph represents a specific region, such as Americas, China, India, etc. In addition to the regions, I need a line that represents the Total amount of downloads. I have one table from a database (Table1). The only columns of the table that are relevant to this graph are Date and Region. 

I have also created a calculated table that contains a single column of all unique regions, with an added row for "Total".  There is no relationship between the two tables. It is created as follows: 

Total = UNION ( VALUES (Table1[Region] ), ROW ( "Total", "Total" ) )

I created a Measure for Table1:

Measure =
VAR _CurrentAxis = SELECTEDVALUE(Total[Region])
RETURN
IF( _CurrentAxis = "Total",
CALCULATE( COUNT(Table1[Region]), VALUES(Total[Region]) ),
CALCULATE( COUNT(Table1[Region]), Table1[Region] = _CurrentAxis ) )

In the report itself, for the linechart I marked the following fields as such:

Axis: Table1[Date]

Legend: Total[Region]

Values: Table1[Measure]

I also created a Slicer:

Field: Total[Region]

Now, here lies the problem. When I select which regions to display in the slicer, everything displays correctly and as expected except for the total line. The total line shows the total number of downloads of all regions, regardless if they are selected in the slicer or not. I want this total line to dynamically graph the total based on what is selected in the slicer.

I suspect the problem lies within the filter of CALCULATE in this line of the Measure: 

CALCULATE( COUNT(Table1[Region]), VALUES(Total[Region]) ),

I tried a few ways to alter this line to no avail. Essentially, I think I need to filter with something along the lines of below, but I don't know how to do that within DAX.

foreach value in VALUES(Total[Region]) { Table1[Region] = value }

Am I going about this the wrong way and is there actually a much simpler approach, or is there something else that I'm missing to get the Total line in my line chart to dynamically graph based on slicer selections?

[parry2k]

@pcrawfor I'm not clear what was not working and with change what started working. Can you explain? I want to understand. It is not clear why it wouldn't work until you have different requirements/expectations.

[pcrawfor]

@parry2k I apologize, I just retested your solution and it worked for me. I think I might have initially replaced one of the tables incorrectly when I first tried it. I will mark your reply as the correct solution! Thank you

[parry2k · Accepted solution]

@pcrawfor solution attached.

@edhans , not sure if not having that function I'm missing anything. Never had a need for it but it can be surely handy. 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[sdeshmukh]

I am creating line chart that shows units sold by location over the period of time. I wanted to add dynamic total line that should shoe total of filtered location units sold and if there is no filter then it should show total units for all location. how can I do that?

[pcrawfor]

@parry2k Thank you so much for your help! Your solution didn't work exactly for my case, but it was very close. With some minor tweaks based on your answer, the issue I was having is resolved. 

Create a measure of Total Sales 

Total Sales = COUNT ( Table1[Region] ) 

Create a measure for Selected Sales to be used in Values of the line chart:

Selected Sales = 
VAR _CurrentAxis = SELECTEDVALUE(Total[Region])

return
IF (  _CurrentAxis  = "Total",
    CALCULATE (
        [Total Sales],
        TREATAS ( ALLSELECTED ( Total[Region] ), Table1[Region] )
    ),
     CALCULATE(
        COUNT(Table1[Region]),
        Table1[Region] = _CurrentAxis
    )
)

EDIT: @parry2k did have the correct solution, it was my mistake in copying it over to my own file. Please see their solution

[parry2k]

@pcrawfor I guess this is what you are looking for:

[pcrawfor]

Yes, that is exactly what I am looking for

[parry2k]

@pcrawfor Is this what you are looking for, basically select the values in the slicer and always show the total of the selected points, as shown in the video below:

[pcrawfor]

Not quite. Each selected value in the slicer should be it's own line. Total should also be an option in the slicer, and if that is selected, it takes the total of everything else selected and graphs that in addition to the other lines.

In my picture below, it should graph the count of Latin America and Taiwan over a date range. Total is the purple line and it is flat at 0, which is incorrect. For Jan 2021, the total should be at 31 (10 + 21).

[parry2k]

@edhans I hope this small video will help with the solution and here is the DAX code:

Selected Order = 
VAR __values = VALUES ( 'Order'[Order] )
RETURN
"Selected Values -> " & 
    CONCATENATEX ( 
        __values, 
        [Order], 
        ", " 
) & 
" Total Count of selected values : " & 
    FORMAT ( 
        COUNTROWS ( __values ), 
        "General Number" 
    ) 

[edhans]

Right @parry2k  - first of all thanks for jumping in with the follow up for @pcrawfor as I would have had to start over.

But my point was, and your video shows it, if you select 5 items, or 0 items, the measure shows 5 selected. Because VALUES(Table[Field]) shows the same with none or all of the items in the field are selected. That may or may not have any impact on this solution. It is just a frustrating thing. I am to sure how to detect none are selected, because DAX shows the same results as when all are selected. No distinction from the slicer's point of view. I know when you select none, it shows all, so treats it as all. Still. No COUNTCHECKBOXESINSLICER() function exists. 😀

[parry2k]

@pcrawfor change @edhans code to use multiple selection:

count = 
VAR varSelectedValueTable = VALUES('Table (2)'[Column1])
//VAR varSelectedValueTable = { varSelectedValue }
VAR varFilteredTable = 
    FILTER(
        'Table',
        'Table'[Column1] in varSelectedValueTable
    )
VAR varTotalCount = 
    CALCULATE(
        COUNTROWS('Table'),
        KEEPFILTERS('Table'[Column1] in varSelectedValueTable)
    )
VAR Result = COALESCE(varTotalCount, 0)
RETURN
    Result

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[edhans]

Correct me if I am wrong @parry2k but with that code, if nothing is selected, isn't everything selected? That might be what @pcrawfor wants, but I do not think that will return nothing if no slicer selections are made.

[pcrawfor]

@edhans, @parry2k: If nothing is selected, then I am ok with "everything" being selected. 

Thank you both for helping me solve this issue. The count is now showing correctly, but there is another issue which my post asks. Getting this to properly show on a line chart. When I try to graph this, the total is 0 despite the count.

[edhans]

Here is one way to do at @pcrawfor 

count = 
VAR varSelectedValue = SELECTEDVALUE('Table (2)'[Column1])
VAR varSelectedValueTable = { varSelectedValue }
VAR varFilteredTable = 
    FILTER(
        'Table',
        'Table'[Column1] in varSelectedValueTable
    )
VAR varTotalCount = 
    CALCULATE(
        COUNTROWS('Table'),
        KEEPFILTERS('Table'[Column1] in varSelectedValueTable)
    )
VAR Result = COALESCE(varTotalCount, 0)
RETURN
    Result

What this does is it takes the Selected Value from your slicer table (disconnected) and turns it back into a table - that is what the {} syntax does. I then added a bit more logic so it returns 0 for those values not selected, not a blank.

Then I count the rows in the main table where the values in column 1 of that table exist in the table created from the slicer selection.

[pcrawfor]

I only get a count if one value is selected in the slicer. If multiple values are selected, the count is 0, which is undesirable. I need to be able to select mutiple values at the same time and get the total count for all of them.

          

[edhans]

Ok, that would have been really helpful to know in the original post. Unfortunately I didn't save my work so I will have to circle back to this later.