Convert measure to column

[Uzi2019]

Hi Experts,

I have created measure which is given below.

BUCKETS =

SWITCH(TRUE(),

[Rank1 Difference]>0 && [Rank2 Difference]>0,"A",

[Rank1 Difference]<0 && [Rank2 Difference]<0,"B",

[Rank1 Difference]<0 && [Rank2 Difference]>0,"C",

[Rank1 Difference]>0 && [Rank2 Difference]<0,"D")

I have 2 measures Rank1 Difference and Rank2 Difference and based on them I created one Bucket Measure as mentioned above which is working perfectly fine. But issue is I need this Bucket as a Column when I tried to convert the same into column so it gives me incorrect result.

Could anyone please help me convert this measure into Column.?

Thank you in advance.

[v-zhenbw-msft · Accepted solution]

Hi @Uzi2019 ,

We can create a calculate table and add a calculate column to meet your requirement.

1. Create a new table,

Table =
ADDCOLUMNS (
    FILTER (
        SUMMARIZE ( Query1, Query1[ASIN_DATE], Query1[ASIN_SOURCE] ),
        Query1[ASIN_SOURCE] <> BLANK ()
    ),
    "SEARCH_RANK", CALCULATE ( SUM ( Query1[SEARCH_RANK] ) ),
    "SHIPPED_COGS", CALCULATE ( SUM ( Query1[SHIPPED_COGS] ) )
)

2. Then we can create a column to get the result.

BUCKETS =
VAR _last_month_date =
    DATE ( YEAR ( 'Table'[ASIN_DATE] ), MONTH ( 'Table'[ASIN_DATE] ) - 1, DAY ( 'Table'[ASIN_DATE] ) )
VAR _Prev1 =
    CALCULATE (
        SUM ( 'Table'[SEARCH_RANK] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ASIN_DATE] = _last_month_date
                && 'Table'[ASIN_SOURCE] = EARLIER ( 'Table'[ASIN_SOURCE] )
        )
    )
VAR _Curr1 =
    CALCULATE ( SUM ( 'Table'[SEARCH_RANK] ) )
VAR _Diff1 = _Prev1 - _Curr1
VAR _result1 =
    IFERROR ( ( _Diff1 / _Prev1 ), 0 )
VAR _Prev2 =
    CALCULATE (
        SUM ( 'Table'[SHIPPED_COGS] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ASIN_DATE] = _last_month_date
                && 'Table'[ASIN_SOURCE] = EARLIER ( 'Table'[ASIN_SOURCE] )
        )
    )
VAR _Curr2 =
    CALCULATE ( SUM ( 'Table'[SHIPPED_COGS] ) )
VAR _Diff2 = _Curr2 - _Prev2
VAR _result2 =
    IFERROR ( ( _Diff2 / _Prev2 ), 0 )
RETURN
    SWITCH (
        TRUE (),
        _result1 = 0
            && _result2 = 0
            && 'Table'[SHIPPED_COGS] <> BLANK (), "Impactor",
        _result1 < 0
            && _result2 < 0, "Inhibator",
        _result1 < 0
            && _result2 > 0, "Rank Negative Revenue Positive",
        _result1 > 0
            && _result2 < 0, "Rank Positive Revenue Negative"
)

3. At last, we need to create a relative column in each table and create an one-to-many relationship between them.

Column = Query1[ASIN_DATE] &"-"& Query1[ASIN_SOURCE]

Column = 'Table'[ASIN_DATE] &"-"& 'Table'[ASIN_SOURCE]

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

[v-zhenbw-msft]

Hi @Uzi2019 ,

If you want to convert measure into column, you need to convert filter context into row context. So the logic of your measures and the structure of your table are important.

If you want to convert the measures’ result into column, maybe is possible, but if you want to use a slicer to control column, it is not possible.

Please refer the following measure and column.

Could you please provide a mockup sample based on fake data and describe what are Rank1 difference and Rank2 difference?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

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

[Uzi2019]

@v-zhenbw-msft  Any luck on calculated column? I have shared pbix file also.

[v-zhenbw-msft]

Hi @Uzi2019 ,

How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ zhenbw

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

[Uzi2019]

@v-zhenbw-msft 

please find Pbix file in link below. I want to convert my measure bucket to column value becuase i want to show bucket in pie chart. so please help me to create bucket column.

https://drive.google.com/file/d/1jUCjEAwDeXfMu2d9M2PuvBMqvsHNsCb_/view?usp=sharing

thank you in advance

[az38]

Hi @Uzi2019 

to use BUCKETS measure as a column you have to recreate Rank 1 and 2 as a columns.

[Uzi2019]

@az38   I have tried creating column also. it is giving me error of circular dependancy. I have shared Pbix file.  you can also try there..

[az38]

@Uzi2019 

I'm not sure with your desired result but you can try smth like

BUCKETS Column = 
VAR Prev1 = CALCULATE(SUM(Query1[SEARCH_RANK]),DATEADD(Query1[ASIN_DATE],-1,MONTH), ALLSELECTED(Query1))
VAR Curr1 = CALCULATE(SUM(Query1[SEARCH_RANK]))
VAR Rank1 =  DIVIDE((Prev1 - Curr1), Prev1, 0)
VAR Prev2 = CALCULATE(SUM(Query1[SHIPPED_COGS]),DATEADD(Query1[ASIN_DATE],-1,MONTH), ALLSELECTED(Query1))
VAR Curr2 = CALCULATE(SUM(Query1[SHIPPED_COGS]))
VAR Rank2 =  DIVIDE((Prev2 - Curr2), Prev2, 0)
RETURN 
SWITCH(TRUE(),
    Rank1 > 0 && Rank2 > 0,"Impactor", 
    Rank1 < 0 && Rank2 < 0,"Inhibator",
    Rank1 < 0 && Rank2 > 0,"Rank Negative Revenue Positive",
    Rank1 > 0 && Rank2 < 0,"Rank Positive Revenue Negative")

[Uzi2019]

@az38 I tried your logic but showing incorrect result.showing impactor in all rows.

[amitchandak]

@Uzi2019 , that is not possible. Try using binning

https://www.daxpatterns.com/dynamic-segmentation/

https://radacad.com/grouping-and-binning-step-towards-better-data-visualization
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...

[Uzi2019]

@amitchandak  why it is not possibe.? can you please explain?

[amitchandak]

@Uzi2019 , measure to the column is not possible. Measure to measure formula is possible,