Apply same calculation for more columns in desktop

[kathraji]

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

Measure	Week1	Week2	Week3
A	5376339	5376339	5376339
B	2949.009	2826.643	55126.325
C%	0.000548516	0.000525756	0.010253506
D%	0.496880584	0.496880584	0.496880584

Desired Output

Measure	Week1	Week2	Week3
A	5,376,339.00	5,376,339.00	5,376,339.00
B	2,949.01	2,826.64	55,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

[v-kelly-msft]

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]

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 

[kathraji]

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 · Accepted solution]

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!

[amitchandak]

Can you reformat? Not able to understand the text

[kathraji]

@amitchandak ..Updated..Please check