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

Filtering the columns used in a measure calculation

Hi All,

 

I have 4 columns, lets call the, A,B,C,D

 

Each column has some numbers

 

CompanyABCD

Apple

1

0.20.30.2
Facebook00.40.50.4
Amazon0.610.80.6
Uber0.40.810.9

 

I want to write a measure that finds the average of A,B,C and D for each company.

 

BUT I want the user to be able to select which columns are used in this measure. For example, the user will click A B C and only these will be used in the average calculation. If the user selects B C D only these will be used in the measure. 

 

Thank you for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Filtering the columns used in a measure calculation

@NNaj - I would recommend to unpivot the data to do so, 

 

click transform data
- select company
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and apply

To visualize,
- matrix visual:
- add country rows,
- add attribute on columns, you can also use this in slicer, to select respective A/B/C/D
- add value on values section, change aggregation to average

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

13 REPLIES 13
Highlighted
Super User IV
Super User IV

Re: Filtering the columns used in a measure calculation

@NNaj - I would recommend to unpivot the data to do so, 

 

click transform data
- select company
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and apply

To visualize,
- matrix visual:
- add country rows,
- add attribute on columns, you can also use this in slicer, to select respective A/B/C/D
- add value on values section, change aggregation to average

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Helper III
Helper III

Re: Filtering the columns used in a measure calculation

Thanks for this. I want to use this average calculation on the x-axis of a scatter graph. will this still be possible ?

Highlighted
Super User IV
Super User IV

Re: Filtering the columns used in a measure calculation

@NNaj yup, x and y axis on scatter chart need a measure and this is perfect.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Community Champion
Community Champion

Re: Filtering the columns used in a measure calculation

@NNaj 

 

Another solution using DAX.

Table

CompanyABCD
Apple1111
Facebook4422
Amazon2331
Uber5555

FilterColumnTable

Columns
A
B
C
D

Use this column in the slicer

Then create a DAX measure

 

 

Average = 
var _column=DISTINCT(FilterColumnTable[Columns])
var _a= IF("A" IN _column,SUM('Table'[A]),BLANK())
var _b=IF("B" IN _column,SUM('Table'[B]),BLANK())
var _c=IF("C" IN _column,SUM('Table'[C]),BLANK())
var _d=IF("D" IN _column,SUM('Table'[D]),BLANK())
var _total= {_a,_b,_c,_d}
var _result= AVERAGEX(_total,[Value])
return _result

 

 

Capture.JPG



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂

Highlighted
Helper III
Helper III

Re: Filtering the columns used in a measure calculation

How do I do the 'filter column table' step, to get that table you screenshot?

 

Highlighted
Community Champion
Community Champion

Re: Filtering the columns used in a measure calculation

@NNaj  you can manually create that table using Enter Data option. Or unpivot the duplicate table then keep Attributes column (delete other columns). Then delete duplicate rows. Rename the column headers and query name.

Highlighted
Helper III
Helper III

Re: Filtering the columns used in a measure calculation

Everything is working so far except the measure, it is showing a blank column when I add it to the Table. Where should I create the measure?

Highlighted
Helper III
Helper III

Re: Filtering the columns used in a measure calculation

Hi Nandu,

What is the [Values] in the averagex part of the measure?

The name of my Table is mgmt_score_component and the name of my filtered table is mgmt_score_component (3) . mgmt_score_component (3) only had one column called Attribute

Highlighted
Helper III
Helper III

Re: Filtering the columns used in a measure calculation

Hi Nandu,

What is the [Value] in the averagex part of the measure?

The name of my Table is mgmt_score_component and the name of my filtered table is mgmt_score_component (3) . mgmt_score_component (3) only had one column called Attribute

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

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

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors