Create a Dax to find source of value while using dependent dax measures in stacked column chart

[sivarajan21]

Hi,

I have a visual as below in my report:

This visual contains a measure called 'Total switch new' as below:

Total switch new =
SUMX ( 'Accruals', [Switch Units] )

'Switch Units' dax referenced above is as below:

Switch Units =
COALESCE (
[Priority 1 units],
[Priority 2 units],
[Priority 3 units],
[Parameter Value units]
)

'Priority 1 units','Priority 2 units', 'Priority 3 units' & 'Parameter Value units' used above are all dax and they are:

Priority 1 units =
SWITCH (
SELECTEDVALUE ( 'Parameter 4'[Parameter Fields] ),
"'DAX_Units'[Profile units]", [Profile units],
"'DAX_Units'[Direct units]", [Direct units],
"'DAX_Units'[Target units]", [Target units],
"'DAX_Units'[Priority 4]", [Units Parameter Value]
)

Priority 2 units =
SWITCH (
SELECTEDVALUE ( 'Parameter 5'[Parameter Fields] ),
"'DAX_Units'[Profile units]", [Profile units],
"'DAX_Units'[Direct units]", [Direct units],
"'DAX_Units'[Target units]", [Target units],
"'DAX_Units'[Priority 4]", [Units Parameter Value]
)

Priority 3 units =
SWITCH (
SELECTEDVALUE ( 'Parameter 6'[Parameter Fields] ),
"'DAX_Units'[Profile units]", [Profile units],
"'DAX_Units'[Direct units]", [Direct units],
"'DAX_Units'[Target units]", [Target units],
"'DAX_Units'[Priority 4]", [Units Parameter Value]
)

Parameter Value units comes from Custom Units table

Above 'Priority 1 units','Priority 2 units', 'Priority 3 units' dax measures  contains 3 base dax measures as below(i have given only name here and you will find their measures in attached file):

Profile units

Direct units

Target units

We have used 3 field parameter tables(Parameter 4,Parameter 5 & Parameter 6) to be used as a slicer for reporting.

FYR, i have given table format in page 1 which will give you an idea on how above measures are dependent on each other and how field parameters are used.

Now, I need to show in below visual whether their monthly values is made up of/composed of Profile units or Direct units or Target units (in different colors)

For example, month of july, Total switch new dax shows that value 1166 comes from Profile units(refer page 1 in report) & November, Total switch new dax shows value 910 comes from Target. 

Then our expected visual would display something like below(different color code to show whether it comes from Profile or Direct or Target):

PFA file here Financial Management -Tanvi Trial - Copy.pbix

Thanks in advance!

@SamWiseOwl @Ahmedx @Greg_Deckler @marcorusso @jgeddes @Anonymous @Anonymous 

[Anonymous]

Thank you amitchandak 

Hi, @sivarajan21 

Based on your description, I created a measure using the following DAX expression:

Color = 
VAR _table = SUMMARIZE('Calendar','Calendar'[Month],"Total switch new",[Total switch new],"Switch Units",[Switch Units],"Priority 1 units",[Priority 1 units],"Priority 2 units",[Priority 2 units],"Priority 3 units",[Priority 3 units])
VAR _current_month = SELECTEDVALUE('Calendar'[Month])
VAR _value1 = MAXX(FILTER(_table,'Calendar'[Month]=_current_month),[Priority 1 units])
VAR _value2 = MAXX(FILTER(_table,'Calendar'[Month]=_current_month),[Priority 2 units])
VAR _value3 = MAXX(FILTER(_table,'Calendar'[Month]=_current_month),[Priority 3 units])
RETURN SWITCH(TRUE(),
    _value1<>BLANK(),"Red",
    _value2<>BLANK(),"Blue",
    _value3<>BLANK(),"Yellow"
)

Apply this measure in the conditional formatting of your column chart:

The results are as shown above.

Best Regards

Jianpeng Li

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

[sivarajan21]

Hi @Anonymous ,

Thanks for your amazing & quick solution!

Thanks @amitchandak 

Apologise for not being clear!

Is there any way we can use this measure in legend so that we can identify/breakdown whether this value comes from Profile or Direct or Target as shown below:

The main focus is not to just color code the bar with different colors but to breakdown from where(Profile or Direct or Target) is coming from?

For example, in your visual, when hovered over month of october it still shows 'Total switch new' as 1193.

Expected result should show Profile or Direct or Target and their values instead of Total switch new.

for example, above barchart visual, if we create a measure and drag it in legend, it has to show 1166 comes from Profile units for month of july 

Please let me know if you need further info!

Thanks in advance!

@Anonymous @amitchandak @jgeddes @Ahmedx @Greg_Deckler @marcorusso @Anonymous @Anonymous 

[Anonymous · Accepted solution]

Hi, @sivarajan21 

Thank you very much for your reply. First, for the points you mentioned:

You can try looking for this tagged visual from the visual marketplace and add it to your report.
You have two options:
Adjust the color to the color that the measure refers to, or change the color in the measure to the color of the markup visual.
For tooltips, when we hover over the content displayed, you can try to customize it in the tooltips.
In general, it is difficult to achieve the effect you mentioned directly.
Especially when it comes to implementing custom tooltips, this is difficult to implement since the field used by your column chart Y-axis uses a measure that references another measure.

Customizing tooltips in Power BI Desktop - Power BI | Microsoft Learn

Best Regards

Jianpeng Li

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

[sivarajan21]

Thanks for your quick response @Anonymous 

This is very close answer to my question! so I will accept it as solution for now!

I will get back latter with a new thread

Thanks!

[amitchandak]

@sivarajan21 , Based on what I got, You can create a measure like this and use that in conditional formatting using the field value option

Color Code =
SWITCH(
TRUE(),
NOT ISBLANK([Priority 1 units]), "Orange",
NOT ISBLANK([Priority 2 units]), "Red",
NOT ISBLANK([Priority 3 units]), "Green",
NOT ISBLANK([Parameter Value units]), "Blue",
"Grey"
)

How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/...
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pan...