Convert Mesure to Dimension

[AlexWest]

Hi guys!
I have an expression:

if( Sum([ABC_SUM]) / Sum(total <ABC_REGION, ABC_INDUSTRY>[ABC_SUM]) >= 0.8, 'A',

if( Sum([ABC_SUM]) / Sum(total <ABC_REGION, ABC_INDUSTRY>[ABC_SUM]) >= 0.5, 'B', 'C'))

But here the problem: I can't convert the expression to dimension so that I could use it for Filter or for Dimesion in the table.

Could you please help me?

Or may be there is the way to write something like this expression in the script?

Thanks a lot in advance!

[BrunPierre]

Try the below as calculated dimension.

=Aggr(if(Sum([ABC_SUM]) / Sum(total <ABC_REGION, ABC_INDUSTRY>[ABC_SUM]) >= 0.8, 'A',
if( Sum([ABC_SUM]) / Sum(total <ABC_REGION, ABC_INDUSTRY>[ABC_SUM]) >= 0.5, 'B', 'C')), ABC_REGION, ABC_INDUSTRY)

[AlexWest]

Hi! Thanks for the answer, but result is 'A' in every row.

[Gabbar]

To create a measure into dimension you need a relative dimension to aggregate on,
like if you have three fields, 
Category,State,Sales

If you want your measure Sum(Sales) to be a Dimesnion you need to aggregate it to either of the fields, but aggregating in on Sales will just give you Sales value. So better to aggregate it on Category,State.

Try:-
Aggr(

if( Sum([ABC_SUM]) / Sum(total <ABC_REGION, ABC_INDUSTRY>[ABC_SUM]) >= 0.8, 'A',

if( Sum([ABC_SUM]) / Sum(total <ABC_REGION, ABC_INDUSTRY>[ABC_SUM]) >= 0.5, 'B', 'C'))

,Your_Neccessary_Dimension).


Remeber Aggregates work like you are joining a table using group by column, Those Group by Columns are 'Your_Neccessary_Dimension'.

[AlexWest]

Hi! Thanks for the answer. Previous answer was the same. Unfortunately I tried to make like this, but it doesn't work. Always shows an 'A' in every row.

[AlexWest · Accepted solution]

I found the answer. I modified my script and solved the issue:

NoConcatenate

  [ABC_Taxes_1]:

  Load 

      ABC_Client

      ,ABC_SUM_1

  resident t1;

  left join

  LOAD

      ABC_Client

      ,ABC_CLIENT_NAME

      ,ABC_INDUSTRY

      ,ABC_REGION

  resident t2;

NoConcatenate

  [ABC_1]:

  Load 

      ABC_Client

      ,ABC_CLIENT_NAME

      ,ABC_INDUSTRY

      ,ABC_REGION

      ,ABC_INDUSTRY&' '&ABC_REGION as ABC_INDUSTRY_IN_REGION //creating composite key

      ,sum(ABC_SUM_1) as [ABC_SUM_1]

  resident ABC_Taxes_1

  group By

      ABC_Client

      ,ABC_CLIENT_NAME

      ,ABC_INDUSTRY

      ,ABC_REGION;

  drop Table ABC_Taxes_1;

Left Join

  load 

  ABC_INDUSTRY_IN_REGION

  ,sum([ABC_SUM_1]) as ABC_SUM_INDUSTRY_IN_REGION

  resident ABC_1

  Group by ABC_INDUSTRY_IN_REGION;

//now making new solid table to calculate my ABC analysis XD

NoConcatenate

  ABC:

  Load

    *

    ,if(ABC_SUM_1/ABC_SUM_INDUSTRY_IN_REGION>= .8, 'A'

    ,if(ABC_SUM_1/ABC_SUM_INDUSTRY_IN_REGION>= .5, 'B' 

        ,if(ABC_SUM_1/ABC_SUM_INDUSTRY_IN_REGION< .5, 'C')))

as ABC_CLIENTS

    ,ABC_SUM_1/ABC_SUM_INDUSTRY_IN_REGION as percentage; //just for test

  Load

  ABC_BIN

    ,ABC_CLIENT_NAME

    ,ABC_INDUSTRY

    ,ABC_REGION

    ,ABC_INDUSTRY_IN_REGION

    ,[ABC_SUM_1]

    ,ABC_SUM_INDUSTRY_IN_REGION

  Resident ABC_1;

  Drop Table ABC_1;