Forum Discussion

ktt777's avatar
ktt777
Helper V
5 years ago
Solved

Changing the column sequences in matrix

Hi 

i have a matrix as below .  How can i arrange the column sequences to  : Base -> Target->Q1-2021->Q2-2021? 

And No column order from 1 to 3 ascending 

Here is the link to power bi file : https://1drv.ms/u/s!ApIDnMK2eKiFgR4xe4rl5XS7WvWl?e=hNb0rb

 

thank you. 

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    Hi, ktt777 

     

    Try to create a measure like this:

    _color = 
var _r=CALCULATE([measure],FILTER('Test 2','Test 2'[Period]="Target"))
var _ifIsQ1Q2=
    IF(MAX('Test 2'[Period]) in {"Q1-2021","Q2-2021"},
    IF([measure]>_r,"red","green")
    )
return _ifIsQ1Q2

    Result:

    Please refer to the attachment below for details

     

     

    Hope this helps.

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    5 years ago

    Hi, ktt777 

     

    The sample file provided by @ryan_mayu  has already achieved the purpose very well.
    Please let me describe the implementation process. You need to create a sorting column for the rows and columns in the matrix separately, and the sorting column and the row and column fields are in the same table.

    In your sample I saw that you created a summary field to sort. This is a good idea, but because the summary field comes from your row field, then circular dependencies will occur here, which cannot be sorted by column.

    You can manually create two sorted tables and use the RELATED function to create sorted fields in the same table.

    Please refer to the attachment below for details

     

     

    Hope this helps.

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • ktt777's avatar
    ktt777
    Helper V
    5 years ago

    How can  i format the value in Q1-2021 and Q2-2021 such as being highlighted in green if it is less than target and red if it is more than target?

     

    thanks 

    • v-angzheng-msft's avatar
      v-angzheng-msft
      Community Support
      5 years ago

      Hi, ktt777 

       

      Try to create a measure like this:

      _color = 
var _r=CALCULATE([measure],FILTER('Test 2','Test 2'[Period]="Target"))
var _ifIsQ1Q2=
    IF(MAX('Test 2'[Period]) in {"Q1-2021","Q2-2021"},
    IF([measure]>_r,"red","green")
    )
return _ifIsQ1Q2

      Result:

      Please refer to the attachment below for details

       

       

      Hope this helps.

       

      Best Regards,
      Community Support Team _ Zeon Zheng
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.