cancel
Showing results for
Did you mean:
Helper IV

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

 Company A B C D Apple 1 0.2 0.3 0.2 Facebook 0 0.4 0.5 0.4 Amazon 0.6 1 0.8 0.6 Uber 0.4 0.8 1 0.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.

1 ACCEPTED SOLUTION
Super User

@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 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!

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

13 REPLIES 13
Community Champion

Another solution using DAX.

Table

 Company A B C D Apple 1 1 1 1 Facebook 4 4 2 2 Amazon 2 3 3 1 Uber 5 5 5 5

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

Appreciate with a kudos
🙂

Helper IV

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

Community Champion

_total is a dynamic table. Its column header is Value. So you have to use it as it is.

Community Champion

I have modified the DAX based on your table names and column names

``````Average =
var _column=DISTINCT(mgmt_score_component (3)[Attribute]) // Use this attribute column in your slicer
var _a= IF("A" IN _column,SUM('mgmt_score_component'[A]),BLANK()) //you have to replace "A" with actual value available in the table mgmt_score_component (3)
var _b=IF("B" IN _column,SUM('mgmt_score_component'[B]),BLANK())
var _c=IF("C" IN _column,SUM('mgmt_score_component'[C]),BLANK())
var _d=IF("D" IN _column,SUM('mgmt_score_component'[D]),BLANK())
var _total= {_a,_b,_c,_d}
var _result= AVERAGEX(_total,[Value])
return _result``````

Appreciate with a kudos
🙂

Helper IV
Why is this not working?

Average =
var _column=DISTINCT('mgmt_score_component (3)'[Attribute])
var _a= IF("NOx disclosed?" IN _column,SUM(mgmt_score_component[NOx disclosed?]),BLANK())
var _b=IF("SOx disclosed?" IN _column,SUM(mgmt_score_component[SOx disclosed?]),BLANK())
var _c=IF("Flaringdisclosed?" IN _column,SUM(mgmt_score_component[Flaringdisclosed?]),BLANK())
var _d=IF("Methan emissioncdisclosed?" IN _column,SUM(mgmt_score_component[Methan emissioncdisclosed?]),BLANK())
var _e= IF("Flaring reductionTarget" IN _column,SUM(mgmt_score_component[Flaring reductionTarget]),BLANK())
var _f=IF("Methane Reduction Target" IN _column,SUM(mgmt_score_component[Methane Reduction Target]),BLANK())
var _g=IF("Carbon Emissiontarget year disclosed?" IN _column,SUM(mgmt_score_component[Carbon Emissiontarget year disclosed?]),BLANK())
var _h=IF("Emissions related Target in place?" IN _column,SUM(mgmt_score_component[Emissions related Target in place?]),BLANK())
var _i= IF("Carbon Emission Reduction Target effort" IN _column,SUM(mgmt_score_component[Carbon Emission Reduction Target effort]),BLANK())
var _j=IF("Emissions relatedpolicy inplace?" IN _column,SUM(mgmt_score_component[Emissions relatedpolicy inplace?]),BLANK())
var _k=IF("EnvironmentProvisions disclosed?" IN _column,SUM(mgmt_score_component[EnvironmentProvisions disclosed?]),BLANK())
var _l=IF("RenewEnergy Usage disclosed?" IN _column,SUM('Table (12)'["RenewEnergy Usage disclosed?"]),BLANK())
var _m= IF("Enviroment RelatedInvestments disclosed?" IN _column,SUM(mgmt_score_component[Enviroment RelatedInvestments disclosed?]),BLANK())
var _n=IF("ClimateChangeRisksOpprtunity Awareness" IN _column,SUM(mgmt_score_component[ClimateChangeRisksOpprtunity Awareness]),BLANK())
var _o=IF("Energy_efficiencyeffort" IN _column,SUM(mgmt_score_component[Energy_efficiencyeffort]),BLANK())
var _p=IF("Clean_energyeffort" IN _column,SUM(mgmt_score_component[Clean_energyeffort]),BLANK())
var _total= {_a,_b,_c,_d,_e, _f, _g, _h, _i, _j, _k, _l, _m, _n, _o, _p}
var _result= AVERAGEX(_total,[Value])
return _result
Community Champion

Unpivot the columns

Then click close and apply

Now create a DAX measure

``AverageValue = AVERAGE('MyTable'[Value])``

Then plot the visuals

Appreciate with a kudos
🙂

Helper IV

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

Community Champion

@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.

Helper IV

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

Helper IV

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?

Super User

@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 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!

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

Helper IV

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

Super User

@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!

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

Announcements

#### Launching new user group features

Learn how to create your own user groups today!

#### 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.

Top Solution Authors
Top Kudoed Authors