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!

13 REPLIES 13
Highlighted

@NNaj 

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

Highlighted

@NNaj 

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



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

Highlighted

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
Highlighted

@NNaj 

 

Follow the below steps. 

Unpivot the columns

step1.JPG

Then click close and apply

step2.JPG

Now create a DAX measure

AverageValue = AVERAGE('MyTable'[Value])

Then plot the visuals

 step3.JPG



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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors