Not sum but original value despite multiple entries

[damit23183]

Hi,

I have column chart where I would like to add "Amount" on X - axis and on Y - axis it will be Full Name and Intitution Name. Please see table below,

When i create column chart to make it Hiearachy in form of FULL NAME as Parent and Insitution Name as Child (On y-Axis) and Amount on X-axis as sum. 

What I am getting is when drill down AB to institution name,

chart getting me $1,860 (Sum of 620) but I would only like to see $620 for AB to Original and $500 for AB to Old.

This amount is belong to Institution name not full name.

Any advise or suggestion?

Thanks

[V-yubandi-msft]

Hi @damit23183 ,
When you have a moment, please review my latest response along with the attached PBIX file and let us know if any changes are needed

[damit23183]

Thank you all for your responses. Really Appreciate it.

I tried but its not working somehow its partially working for example,

If i simply enable drill down arrow and add other catergories into the chart in this pattern;

Full Name  --> Institution Name (On  Y-Axis)

Ammount  (X Axis)

Chart is working partially where based on Image I have attached above in question, 

Only Two full name (BC & CD ) are coming with $620 correct amount. However, 3rd full name AB is coming with only one amount $500. Instead, when i drill down AB it should come up with two column $620 for Original and $500 for Old Instituion Name.

I did check relationship in data model too and all are coming from same table.

Thanks

[V-yubandi-msft · Accepted solution]

Hi @damit23183 ,

Thank you for providing the details. Based on your explanation, the issue isn't with the hierarchy, but with how Power BI calculates the Amount when drilling into AB. Since only $500 appears instead of both $620 and $500, it seems Power BI is aggregating at the Full Name level and not keeping Institution Name in context.

To resolve this, you can create a measure as follows

Amount by Institution =
CALCULATE(
    SUM(Data[Amount]),
    ALLEXCEPT(Data, Data[Full Name], Data[Institution Name])
)

This will ensure the Amount is calculated for each Institution Name under each Full Name. With this measure, drilling into AB will display two bars—$620 for Original and $500 for Old.

Be sure to use this measure in the Values field of your column chart, not the raw Amount column. This should provide the breakdown you expect.

FYI:

I have attached the PBIX file for your reference. If you continue to notice any issues, please let me know and I will be happy to help further.

Regards,
Yugandhar.

[V-yubandi-msft]

Hi @damit23183 ,
Could you let us know if your issue has been resolved or if you need any further assistance.

Thank you.

[V-yubandi-msft]

Hi @damit23183 ,
Just add to @Ashish_Mathur, response.

1. Use Drill Down (single arrow) to review each Full Name separately. This way, you'll get an accurate breakdown for each Institution Name. 

2. It's best to avoid Expand All (double arrow) since it can combine unrelated rows and result in incorrect totals.

Thanks.

[Ashish_Mathur]

Hi,

Write this measure and drag it to your visual

Measure = min(Data[Amount])

Hope this helps.

[FritsOrdelman]

To display the original value of a field in Power BI — even when there are multiple entries — without aggregating (like summing), you can use the DAX function SELECTEDVALUE().

This function returns the value only if there is a single unique value in the current context. If there are multiple values, it returns a fallback (e.g. a message or blank).

OriginalValue = SELECTEDVALUE(Table[Column], "Multiple values")

This will:

• Show the original value if only one exists.
• Show "Multiple values" if there are several different values in the context.

This approach avoids automatic aggregation and ensures your visual reflects the actual value when appropriate.

[damit23183]

Thanks for your response.

In the logic you given, what is Multiple Values?

I didnt understand how will this fit into Measure.

Thanks,