ALLSELECTED on Calculated Column not working

[Borrek]

Hi,

I tried to find the solution to my problem for over a while. Unfortunately without success. 

I do have payments statuses in my database that are not properly formatted for end user. So I created calculated column using switch formula. I did some charts and created measures. One of them is a stacked column chart where revenue is distributed over months and grouped by payments status (based on calculated column).

As I show absolute numbers it is advised to show as well % of subtotal for each status in the tooltip. I created a measure where I divide absolute number by subtotal.

DIVIDE([SalesValue],CALCULATE([SalesValue],ALLSELECTED(Oders_table[payment_type_switch])))

This is where my problem starts. If I use in chart and formula calculated column always 100% is returned (allselected is not working). If I use not formatted column I do have proper results:

DIVIDE([SalesValue],CALCULATE([SalesValue],ALLSELECTED(Oders_table[payment_type)))

How to calculate % of subtotal having in chart formatted statuses?

[v-lionel-msft]

Hi @Borrek ,

Or try this.

DIVIDE(
    [SalesValue],
    CALCULATE(
        [SalesValue],
        ALLEXCEPT( Oders_table, Oders_table[payment_type_switch] )
    )
)

If the problem is still not resolved, please provide sample data and screenshotes of the visual.

Best regards,
Lionel Chen

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

[mhossain]

Hi @Borrek 

Try this DIVIDE([SalesValue],CALCULATE([SalesValue],ALL(Oders_table))

[Borrek]

Hi, thanks for answering but this eliminates all filters - I need to eliminate only payment status filter.

What is wired that the only difference between working and not working formula is the column. If I use column imported from database (and change legend in chart respectively) it works. If I change both to calculated column (SWITCH function on imported column) it does not.

I read about shadow filter so I also tried to insert both columns in ALLSELECTED but it does not work as well.

[amitchandak · Accepted solution]

@Borrek , try removefilters

removefilter(Table[payment status])

refer example on

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

[Borrek]

The problem was that ALLSELECTED should be not only imposed on payment_type calculated column but also calculated column that was used to sort payment_type column.

Not sure why but sorting by other column affected contex filters of column I used. I was able to find it by removing filters.

From my point of view such situation is counterintuitive but this is how it works.

[jackl21an]

I also found this worked; when I included 2 ALLSELECTED statements, one for the dimension and the other for the column I was using to sort the dimension (used to sort the legend in a bar chart). Slight difference to yours is that in my case they were both were held in a separate reference table with a relationship on a calculated column in the main dataset.

[mhossain]

@Borrek 

Can you share the sample pbix with dummy data, can try