URGENT! converted measure to calculated column but it's giving me only 100%

[PriyankaJhaTheA]

Hello Power BI Community,

Attached is the sample pbix: https://drive.google.com/file/d/1Oav8yBIvQzhYOmLAw2qd2tjC0Qm6wtKL/view?usp=sharing

I've created a measure that allows me to calculate the sales contribution % by states:

Combined_LW_PY_Contribution = 
max('Data by Channels'[State]) & ": " &  FORMAT(
DIVIDE(
    SUM('Data by Channels'[Latest Week]),
    CALCULATE(
        SUM('Data by Channels'[Latest Week]),
        // ALL('Data by Channels'[State]),
        'Data by Channels'[State] <> "Total US" && 'Data by Channels'[State] <> "Core 7 States"
    )
), "0.0%")

The data I'm getting is the following and is the expected output:

But I need this measure as a calculated column to use in a map chart, so I tired to convert it into a calculated column, so far I've been unsuccessful. here is the code:

Combined_LW_PY_Contribution_Column = 
VAR CurrentState = 'Data by Channels'[State]
VAR TotalLatestWeek = CALCULATE(
    SUM('Data by Channels'[Latest Week]),
    ALL('Data by Channels'[State]),
    'Data by Channels'[State] <> "Total US" && 'Data by Channels'[State] <> "Core 7 States"
)
VAR StateLatestWeek = CALCULATE(
    SUM('Data by Channels'[Latest Week]),
    FILTER(
        ALL('Data by Channels'[State]),
        'Data by Channels'[State] = CurrentState
    )
)
RETURN
    CurrentState & ": " &  FORMAT(
        DIVIDE(
            StateLatestWeek,
            TotalLatestWeek
        ), "0.0%"
    )

This is what I'm getting as calc column's output:

Please take a look and let me know if you need anything else.
Look forward to your replies.

@MFelix , @amitchandak , @Greg_Deckler , @tamerj1 , @Jihwan_Kim , @Joe_Barry , @Ashish_Mathur, 

[v-xuxinyi-msft]

Hi @PriyankaJhaTheA 

Thanks for the reply from @Jihwan_Kim , please allow me to provide another insight:

I can't open your link because of some security policy. I have created a simple sample to fulfill your needs, hope it helps.

My sample:

modify the formula as follows

Combined_LW_PY_Contribution_Column = 
[State] & ": " &  FORMAT(
DIVIDE(
    CALCULATE(
        SUM('Data by Channels'[Latest Week]),
        'Data by Channels'[State] <> "Total US" && 'Data by Channels'[State] <> "Core 7 States"
    ),
    CALCULATE(
        SUM('Data by Channels'[Latest Week]),
        ALL('Data by Channels'),
        'Data by Channels'[State] <> "Total US" && 'Data by Channels'[State] <> "Core 7 States"
    )
), "0.0%")

Result:

Best Regards,
Yulia Xu

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

[PriyankaJhaTheA]

Hello @v-xuxinyi-msft , 

Thanks for helping me out here. I ran the code you suggested but I'm only getting zero for all the states.

[v-xuxinyi-msft]

Hi @PriyankaJhaTheA 

Since I don't have your data and "Lastest Week" is a column in my test, could you please tell me if your "Lastest Week" is a measure or a column?

Best Regards,
Yulia Xu

[PriyankaJhaTheA]

latest week is also a column in my dataset

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please try something like below.

Combined_LW_PY_Contribution_Column =
VAR CurrentState = 'Data by Channels'[State]
VAR TotalLatestWeek =
    SUMX (
        ADDCOLUMNS (
            FILTER (
                'Data by Channels',
                'Data by Channels'[State] <> "Total US"
                    && 'Data by Channels'[State] <> "Core 7 States"
            ),
            "@value", [Latest Week]
        ),
        [@value]
    )
VAR StateLatestWeek =
    SUMX (
        ADDCOLUMNS (
            FILTER ( 'Data by Channels', 'Data by Channels'[State] = CurrentState ),
            "@value", [Latest Week]
        ),
        [@value]
    )
RETURN
    CurrentState & ": "
        & FORMAT ( DIVIDE ( StateLatestWeek, TotalLatestWeek ), "0.0%" )

[PriyankaJhaTheA]

Hello @Jihwan_Kim 
Thanks a lot for your reply. I think we are close to the solution.
I ran your code as you can see in the screenshot below: 
But the % contribution numbers are a bit different from the measure (Combined_LW_PY_Contribution) I created. 

there are other columns in my data set that might be affecting the column calculation like: "Measures" and "Channel". How do I add them to the filter context of this column?

please take a look at the sample pbix as well if that helps to understand the data a bit better. Thanks a lot again!

[Jihwan_Kim]

Hi,

It is quite difficult for me to understand the filter context that are applied to the measure. I think that is one of the reasons why my calculated column formula does not produce the result that you want.
Could you please consider what filter context are applied to your measure and visualization on the left, especailly the relationships in the datamodel, slicers on the top, and the filters in the filter pane? And then you can try to apply those to the calcualted column.

Thank you.

[PriyankaJhaTheA]

Hello @Jihwan_Kim , 

Thanks for the suggestion. I tried to add all the filters and slicers that were required for my visualization. However I think I'm missing something in the Dax code as I'm getting blank for the % contribution. Can you take a look at the code once? See if I'm missing something.

Thanks a lot!

Combined_LW_PY_Contribution_Column = 
VAR CurrentBrand = max('Competitors Flag'[Products Clean])
VAR CurrentState = 'Data by Channels'[State]
VAR CurrentMeasure = 'Data by Channels'[Measures] // Replace with the actual column name if different
VAR CurrentChannel = 'Data by Channels'[Channel] // Replace with the actual column name if different
VAR TotalLatestWeek =
    SUMX (
        ADDCOLUMNS (
            FILTER (
                'Data by Channels',
                'Data by Channels'[State] <> "Total US"
                    && 'Data by Channels'[State] <> "Core 7 States"
                    && 'Data by Channels'[Measures] = CurrentMeasure
                    && 'Data by Channels'[Channel] = CurrentChannel
                    && max('Competitors Flag'[Products Clean]) = CurrentBrand
                     && max('Competitors Flag'[Brands Sorting]) = 1
            ),
            "@value", [Latest Week]
        ),
        [@value]
    )
VAR StateLatestWeek =
    SUMX (
        ADDCOLUMNS (
            FILTER ( 
                'Data by Channels', 
                'Data by Channels'[State] = CurrentState
                && 'Data by Channels'[Measures] = CurrentMeasure
                && 'Data by Channels'[Channel] = CurrentChannel
                && max('Competitors Flag'[Products Clean]) = CurrentBrand
                 && max('Competitors Flag'[Brands Sorting]) = 1
            ),
            "@value", [Latest Week]
        ),
        [@value]
    )
RETURN
    CurrentState & ": "
        & FORMAT ( DIVIDE ( StateLatestWeek, TotalLatestWeek ), "0.0%" )