cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Create a pie chart with a calculated column that varies with filters

Hi everyone,

I am having problems creating a pie chart that has to change with filters, so far i already create a chart but only works when i don't apply the filters, the moment i apply one filter the chart does not shows the right filtered data, and that's because the chart uses a calculated column named "CHART" that i created with a condition : 

CHART = IF([WEIGHT]>20,"Order greater than 20","Order less than 20")

but when the weight variates deppending on the filters my column with the condition doesn't take on count the new weight filtered. It stays with the total weight.

 

when i use the same code but in a measure instead of in a new column, it actually changes deppending of the new filtered weight, the problem is that i can't use a measure as "Legend" for my pie chart.

 

here's a example of my data base that i recreate in excel (the original actually has more "FILTERS" and data in general):

 

tabla1.png

here's without filters:

 

Screenshot_2.png

and i achieved a chart like this one:

 

pie chart.png

 

it count and divides in a chart how many "ORDER" are "GREATER THAN 20 pounds" and how many not, using "CHART" as Legend and "Count of ORDER" as Value.

I would like to create a calculated column "CHART" that recalculates the values of the weight deppending on the filters (as a measure does), i.e:

 

aaaaaaaaaaa.png

(look that the weight changes deppending on the filters and also the "CHART" column should do it)

 

So i can use this column for my pie chart, or if this is not possible, how can i do more charts like the first one but with the new values of the weight after the filters.

 

I hope i have explained myself enough, english it is not my first language

Thank you all for your help and time. 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Create a pie chart with a calculated column that varies with filters

Right, so create a new table with just this information:

 

Table: ChartLegend

Chart

Greater than 20

Less than 20

 

You will use this as your Legend.

 

Now you are going to need a measure that goes something like:

Measure =
  __Chart = MAX('ChartLegend'[Chart]) //get your current value for your legend
  __Table = 'Table' //get your fact table with current filters
  __Table1 = ADDCOLUMNS('Table',"__Chart",<Some calculation goes here>) //add your dynamic column
RETURN
  SWITCH(__Chart,
    "Greater than 20",COUNTROWS(FILTER(__Table1,__Chart = "Greater than 20")),
    "Less than 20",COUNTROWS(FILTER(__Table1,__Chart = "Less than 20")),
  )
    
  

 

 

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Create a pie chart with a calculated column that varies with filters

In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Create a pie chart with a calculated column that varies with filters

Hi @Greg_Deckler, thank you for your reply, it would be possible that you can help me a little with the DAX command that ties everything at the end? I am very new using DAX Commands and after reading the post i just couldn't translate the DAX solution from that problem to this, specifically the "measures to show" part at the end of the post.

 

Hope you can help and thank you again!!

Highlighted
Super User IV
Super User IV

Re: Create a pie chart with a calculated column that varies with filters

Right, so create a new table with just this information:

 

Table: ChartLegend

Chart

Greater than 20

Less than 20

 

You will use this as your Legend.

 

Now you are going to need a measure that goes something like:

Measure =
  __Chart = MAX('ChartLegend'[Chart]) //get your current value for your legend
  __Table = 'Table' //get your fact table with current filters
  __Table1 = ADDCOLUMNS('Table',"__Chart",<Some calculation goes here>) //add your dynamic column
RETURN
  SWITCH(__Chart,
    "Greater than 20",COUNTROWS(FILTER(__Table1,__Chart = "Greater than 20")),
    "Less than 20",COUNTROWS(FILTER(__Table1,__Chart = "Less than 20")),
  )
    
  

 

 

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Helper I
Helper I

Re: Create a pie chart with a calculated column that varies with filters

Thank you for your help man,

 

In the script when i use 

Measure = 

          __Chart = MAX(...) , "__Chart" is my existing column named "Chart" or is a new one that the measure will create? because the script program sends me a "unexpected expression".
I am not sure if the measure can let me do something like:

            measure = __chart = MAX()  = __table = 'table'

 

Most likely my error is that i'm trying to take that script to literally for my measure.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.