cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jphelps13kantar
New Member

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

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
    )
)

 

 

1 ACCEPTED SOLUTION
mattww
Responsive Resident
Responsive Resident

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.

 

View solution in original post

2 REPLIES 2
mattww
Responsive Resident
Responsive Resident

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.

 

View solution in original post

It works when I replace 

MAX(Sheet1[N]) 

with

SUM(Sheet1[N])

 so thank you! 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.