Forum Discussion

hellodax1's avatar
hellodax1
New Member
3 years ago

Creating a Transposed Table from a Calculated Table in DAX

I have a calculated table that I created to calculate scores for EACH user for EACH transaction (see table below). Currently, I had to use a calculated table to calculate these scores because the score had to be based on the user. The scores (40, 55, 98 etc) are also housed in this table but the scores were aslo labeled depending on the range they fall into. The "simplified" calculated table is as follows:

Calculated Table in DAX

Email122 Label133 Label144 Label155 Label
[email protected]Needs ImprovementNeeds ImprovementAdvanced 
[email protected]NoviceAdvancedAdvancedAdvanced
[email protected]Needs ImprovementAdvancedAdvanced

 

 

[email protected]AdvancedAdvancedAdvancedNeeds Improvement

*there are around 2000+ users and around 30+ columns but these are the main ones I care about (score label)

*not every user is assigned to/will have a score for each transaction 

 

Here is what I'm trying to get the visual to look like this: 

Visual 

Transaction# Advanced# Total% Advanced
1221425%
1333475%
14444100%
1551250%

 

So essentially for EACH transaction, I want to get a total on the number of users who are labeled as "advanced" for that transaction".

 

Since all the calculations had to be done in DAX with a calculated table, I can't transpose this table in PowerQuery. But if there is a way I can transpose the original calculated table to make the 122 label, 133 label as columns instead or rows, the other necessary calculation to get the visual table above can be made

 

Happy to hear any advice! 

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi hellodax1

     

    You can refer to the following two methods:

    Way1: Maybe you can try to use the union() function to try to solve this problem

    e.g 

    Table 3 = union(SELECTCOLUMNS('Table',"aaa",[122 Label]),SELECTCOLUMNS('Table',"bbb",[133 Labe]),SELECTCOLUMNS('Table',"ccc",[144 Label]))

     


     Updating Media

     

    Please refer to the following links:

     Solved: Union only selected columns in new table - Microsoft Power BI Community

    Way 2:

    You can consider completing the relevant calculations in the Excel table first, and then importing the file into Power BI and using Power Query to transpose

     

     Best Regards,

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.