Replicate "Percent of column total" in a line chart (with filters)

[jphelps13kantar]

Research

My issue is similar to the below ones, but I don't know how to handle filters in these

https://community.powerbi.com/t5/Desktop/Exclude-rows-from-sum-based-on-condition/td-p/1166556

https://stackoverflow.com/questions/63570471/visualise-percentage-of-sales-over-time-power-bi-for-ea...

Data & Workbook

My PowerBI file:  

https://1drv.ms/u/s!AgLy4UR8UpdihuBwoVPT3d1ojzdOvw?e=9aLVX6

My real data is the same as this except I have 5 filters e.g Country, Gender, Age etc. instead of just Country 

Problem

The "Percent of column total" in Matrix tables is super powerful. It calculates automatically for me the percentage of my sum over the selected dimension, and changes appropriately when I change filter combinations.  

Line charts unfortunately don't have this option 😞 

I have spend ~2 hours now searching the forums and SO, and the most promising solutions are above. They don't seem to update to sum to 100% though with the filters I have applied. 

So it works when both Germany & UK are selected. However, filtering to say just UK, the values don't sum to 100% like the "percent of column total" does. I expect the solution is simple but I'm not finding this DAX code to be that intuitive as a newbie to the platform. Any help would be greatly appreciated! 

Let me know if any problems accessing the powerbi file or understanding the problem. Thanks 🙂

Edit: Added DAX denominator below. Numerator is SUM(Sheet1[N])

N.denom = 
var current_row_date = MIN(Sheet1[Date])
var current_question = Min(Sheet1[Question])
return 
CALCULATE(
    SUM(Sheet1[N]),
    FILTER(
        ALL(Sheet1),
        Sheet1[Date] = current_row_date &&
        Sheet1[Question] = current_question
    )
)

[mattww · Accepted solution]

Hi @jphelps13kantar ,

Try this

N(% by Date) 2 = DIVIDE(MAX(Sheet1[N]),CALCULATE(SUM(Sheet1[N]),ALLEXCEPT(Sheet1,Sheet1[Date],Sheet1[Question],Sheet1[Country])))

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

[mattww · Accepted solution]

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

[jphelps13kantar]

It works when I replace 

MAX(Sheet1[N]) 

with

SUM(Sheet1[N])

 so thank you! 🙂