Forum Discussion

kathraji's avatar
kathraji
Frequent Visitor
6 years ago
Solved

Apply same calculation for more columns in desktop

Hi Everyone, I'm working on data that looks similar and these 4 columns A, B,%,C, D% are part of the measures group which can be referred by a single column name in MDX/DAX query

 

MeasureWeek1Week2Week3
A537633953763395376339
B2949.0092826.64355126.325
C%0.0005485160.0005257560.010253506
D%0.4968805840.4968805840.496880584

 

Desired Output

 

MeasureWeek1Week2Week3
A5,376,339.005,376,339.005,376,339.00
B2,949.012,826.6455,126.33
C%0.05%0.05%1.03%
D%49.69%49.69%49.69%


This means all that columns if there is a "%" in column name we should add "%" for the values and multiply by 100 and other columns should have been rounded off to two decimal points.
I was able to change the one column ie Week1 to multiple by 100 using custom column but my questions are
1. How I can apply the same to other Weeks Columns? Do I need to create one custom column for each week? I've around 55 such columns. Is there any way I can simply apply the same calculation for other columns as well.
2.How I can add % at the end of column values which has % init


CalculationColumn = IF(ISERROR(SEARCH("%", [[Measures]])Week1,Week1*100

Need Help
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi  kathraji ,

     

    If you need to show all the columns in a table visual,yes ,you need 50 measures,but if you only need show a certain column,you can use  a slicer table to switch the column you need :

    1. create a slicer table first:

    Then using a measure as below:

     

    CalculationColumn =
VAR a =
    ISERROR ( SEARCH ( "%", SELECTEDVALUE ( 'Table'[Measure] ) ) )
VAR b =
    SELECTEDVALUE ( 'Table'[Week1] )
VAR c =
    SELECTEDVALUE ( 'Slicer'[Week] )
VAR d =
    SELECTEDVALUE ( 'Table'[Week2] )
VAR e =
    SELECTEDVALUE ( 'Table'[Week3] )
RETURN
    SWITCH (
        c,
        "Week1", SWITCH (
            a,
            TRUE (), FIXED ( SELECTEDVALUE ( 'Table'[Week1] ), 2 ),
            FALSE (), FORMAT ( b, "percent" )
        ),
        "Week2", SWITCH (
            a,
            TRUE (), FIXED ( SELECTEDVALUE ( 'Table'[Week2] ), 2 ),
            FALSE (), FORMAT ( d, "percent" )
        ),
        "Week3", SWITCH (
            a,
            TRUE (), FIXED ( SELECTEDVALUE ( 'Table'[Week3] ), 2 ),
            FALSE (), FORMAT ( e, "percent" )
        )
    )

     

     

    Then you will see:

     

     

    For the related .pbix file,pls click here.

     

     
    Best Regards,
    Kelly
     
    Did I answer your question? Mark my post as a solution!

6 Replies

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

    Hi kathraji ，

     

    You need a measure as below:

     

    CalculationColumn =
VAR a =
    ISERROR ( SEARCH ( "%", SELECTEDVALUE ( 'Table'[Measure] ) ) )
VAR b =
    SELECTEDVALUE ( 'Table'[Week1] )
RETURN
    SWITCH (
        a,
        TRUE (), FIXED ( SELECTEDVALUE ( 'Table'[Week1] ), 2 ),
        FALSE (), FORMAT ( b, "percent" )
    )

     

     

    Finally you will see:

     

     

    For the related .pbix file ,pls click here.

     

     

     
    Best Regards,
    Kelly
     
    Did I answer your question? Mark my post as a solution!

     

    • kathraji's avatar
      kathraji
      Frequent Visitor
      6 years ago

      Thanks  v-kelly-msft for the update.

       

      How I can apply the same calculation for other weeks?

       

      Do I need to create measure for each column?I have around 50 such columns.

       

      Regards,

      Anil Kumar 

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

        Hi  kathraji ,

         

        If you need to show all the columns in a table visual,yes ,you need 50 measures,but if you only need show a certain column,you can use  a slicer table to switch the column you need :

        1. create a slicer table first:

        Then using a measure as below:

         

        CalculationColumn =
VAR a =
    ISERROR ( SEARCH ( "%", SELECTEDVALUE ( 'Table'[Measure] ) ) )
VAR b =
    SELECTEDVALUE ( 'Table'[Week1] )
VAR c =
    SELECTEDVALUE ( 'Slicer'[Week] )
VAR d =
    SELECTEDVALUE ( 'Table'[Week2] )
VAR e =
    SELECTEDVALUE ( 'Table'[Week3] )
RETURN
    SWITCH (
        c,
        "Week1", SWITCH (
            a,
            TRUE (), FIXED ( SELECTEDVALUE ( 'Table'[Week1] ), 2 ),
            FALSE (), FORMAT ( b, "percent" )
        ),
        "Week2", SWITCH (
            a,
            TRUE (), FIXED ( SELECTEDVALUE ( 'Table'[Week2] ), 2 ),
            FALSE (), FORMAT ( d, "percent" )
        ),
        "Week3", SWITCH (
            a,
            TRUE (), FIXED ( SELECTEDVALUE ( 'Table'[Week3] ), 2 ),
            FALSE (), FORMAT ( e, "percent" )
        )
    )

         

         

        Then you will see:

         

         

        For the related .pbix file,pls click here.

         

         
        Best Regards,
        Kelly
         
        Did I answer your question? Mark my post as a solution!
    • kathraji's avatar
      kathraji
      Frequent Visitor
      6 years ago

       

      Thanks for your suggestion.

       

      I was able to get what I'm looking for.But my data for Table[week1] has null values so when I use this measure,for null values I'm getting value returned as "0.00".Is it possible to just return null or blank value if it doesn't have any value?

       

      Regards,

      Anil Kumar