Forum Discussion

aaron797's avatar
aaron797
Icon for Advocate I rankAdvocate I
6 years ago
Solved

Pivot Measures in Powerbi

Hi,   I have the below "measures" in powerBI that are calculated post data load.   Engaged Disengaged ActivelyDisengaged 36 7 1   I would like to pivot them into the below columns,...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Try this

    union(
SUMMARIZE(table, table[any Group by col], "Engagement","Engaged","CountEngagement" , sum(table[Engaged])),
SUMMARIZE(table, table[any Group by col], "Engagement","Disengaged","CountEngagement" , sum(table[Disengaged])),
SUMMARIZE(table, table[any Group by col], "Engagement","ActivelyDisengaged","CountEngagement" , sum(table[ActivelyDisengaged]))
)

     

    or

     

    union(
    SUMMARIZE(table, "Engagement","Engaged","CountEngagement" , sum(table[Engaged])),
    SUMMARIZE(table, "Engagement","Disengaged","CountEngagement" , sum(table[Disengaged])),
    SUMMARIZE(table, "Engagement","ActivelyDisengaged","CountEngagement" , sum(table[ActivelyDisengaged]))
    )

  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi aaron797 ,

     

    We can create a Calculated table to store the name of enagement, then we can create a measure to calculate the result of each measure:

     

    Calculated Table:

     

    Engagement = DATATABLE("Engagement",STRING,{{"Engaged"},{"Disengaged"},{"Actively Disengaged"}})

     

     

    Measures:

     

    CountEngagement = SWITCH(SELECTEDVALUE(Engagement[Engagement]),"Engaged",[Engaged],"Disengaged",[Disengaged],"Actively Disengaged",[ActivelyDisengaged])

     

     


    Best regards,

     

aaron797's avatar
aaron797
Icon for Advocate I rankAdvocate I
6 years ago

Hi Amit,

 

Thank you for the reply, I am having issues breaking down the below code, it is a bit beyond me, for instance DA Status/AA Status, Net Cal Amount etc. 

 

it would be greatly appreciated if you prepare the union statement with my columns above, or provide some further resources / explanation. 

 

Thank you very much!!

v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi aaron797 ,

 

We can create a Calculated table to store the name of enagement, then we can create a measure to calculate the result of each measure:

 

Calculated Table:

 

Engagement = DATATABLE("Engagement",STRING,{{"Engaged"},{"Disengaged"},{"Actively Disengaged"}})

 

 

Measures:

 

CountEngagement = SWITCH(SELECTEDVALUE(Engagement[Engagement]),"Engaged",[Engaged],"Disengaged",[Disengaged],"Actively Disengaged",[ActivelyDisengaged])

 

 


Best regards,

 

  • aaron797's avatar
    aaron797
    Icon for Advocate I rankAdvocate I
    6 years ago

    This worked perfectly, thank you! very simple approach.