Negative value to display as a specific group in treemap

[Anonymous]

Hi , 

I have tree map chart to show the value with 2 types. But we will have negative values in 'Prov' type.  What I'm thinking is to keep 'Dest' and separate 'Prov' to two parts , 'Prov+' and 'Prov-' , and use abs() for those negative value to show in 'Prov-'. 

Would you please help me on how to do it? 

Group : Type (Column)

Detail : Line (Column)

Values: measure (Measure)

Thanks!

[parry2k]

@Anonymous although the solution I provided does work as it worked in the sample pbix file, for circular dependency there is something else going on. Anyhow,  you are moving on but you need to understand why this dependency error is showing up, check this post here.

Good luck!

[parry2k]

@Anonymous here is a better example, Tau has two records with -ve and +ve, and the sum of both records is+ve and it will show correctly under Prov + bucket.

[Anonymous · Accepted solution]

Hi @parry2k , 

Thank you for your solution. 

I've tried your way in my case. But seems it's same error as before when I created a column to put in dynamic value. 

In this case, I will not be insist on the treemap. I'm trying the stacked bar chart to meet our requirement. 

Thank you all for your kindly help on this case. 

[parry2k]

@Anonymous The solution provided by @PhilipTreacy will work assuming that you want to check negative at the row level (record level) but assuming if a Group + Detail contains some negative and positive records and the sum of it turn out to be positive then this solution will not work. Although I'm not sure if this is the requirement in your case. Anyhow kudos to @PhilipTreacy for the solution.

Here is a solution that doesn't check negative and positive at the row level but check the sum of group + detail combination and then put it in the respective bucket. 

Just throwing out there if this turns out to be the requirement, if not then the solution provided by @PhilipTreacy is the way to go.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[PhilipTreacy]

Hi @Anonymous 

Please download my sample PBIX file with data and visual

Without your data I had to make some up, so I used this table

and transformed it with Power Query to this

and then created this Treemap

Regards

Phil

[Anonymous]

Hi @PhilipTreacy , 

Thank you for your solution. Based on your data structure , that's the way to go. 

For may case, maybe I can give you an example to explain. 

If the calculation will be depends on date. If we select 2020, then Prov of 'Gamma' is negative, but if select 2021 or both selected, the Prov of 'Gamma' is positive. 

[V-pazhen-msft]

@Anonymous 
Circular dependency is a relationship error, the problem is the measure [P&L Impact FM vs M].

Avoiding circular dependency errors in DAX - SQLBI.

Paul

[V-pazhen-msft]

@Anonymous 
I guess you cannot put measure in the group field, create a column should work.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @V-pazhen-msft , 

I created the column, but there is an error - A circular dependency was detected: Depreciation[Column], Depreciation[Commercial Line Group], Depreciation[Column]. it's because the calculation is not based on row, it's acutally based on selected , it's dynamic. 

[amitchandak]

@Anonymous , If you need at line level then column else you need dynamic segmentation

If([value] <0, "Prov", [Dest])

In this case, you need to use the measure to create dynamic segmentation, refer if these can help

https://www.youtube.com/watch?v=CuczXPj0N-k

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[Anonymous]

Hi @amitchandak , 

Thanks for your quick support. 

The expected type value should be Dest , Prov+ and Prov-. 

And even we use a measure to dynamicly separate the positive and negative value in Prov type , we can't apply this measure in group of Tree map chart.