Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • parry2k's avatar
    parry2k
    6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago
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
nandukrishnavs's avatar
nandukrishnavs
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous 

 

Follow the below steps. 

Unpivot the columns

Then click close and apply

Now create a DAX measure

AverageValue = AVERAGE('MyTable'[Value])

Then plot the visuals

 



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