Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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?
Solved! Go to 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
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.
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.
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
95 | |
94 | |
80 | |
71 | |
64 |
User | Count |
---|---|
120 | |
105 | |
99 | |
81 | |
72 |