cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Measure calculate for individual rows but not for categories from related table

Hi,

This is a follow up from another post but given this is a whole new question I'm making a new post.

I have 3 investments - Happy, Sad, and Funny. These investments and their value are kept in 'Investment Data'[Investment Value], which is a measure.

I have another table called 'Investment Region'.

• Happy and Sad are in the USA
• Funny is in Canada

As you can see below, my measure works when visualized for the three investments individually, but when I try to visualize by region only Canada shows because presumably there is only one investment inside.

My question is how can I make USA show on the bottom chart with the total of Happy and Sad for the top chart?

Many thanks in advance!

5 REPLIES 5
Frequent Visitor

@v-zhangti Can you please help with my comment above? Thank you

Community Support

Hi, @vkong6m

The bottom chart does not show USA because he does not have data under the current calculation.

Are the total results for happy and sad as shown in the graph?

``````Happy and Sad =
CALCULATE (
[Investment Value],
FILTER ( ALL ( 'Investment Data' ), [Investment] = "Happy Investment" )
)
+ CALCULATE (
[Investment Value],
FILTER ( ALL ( 'Investment Data' ), [Investment] = "Sad Investment" )
)``````

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

Frequent Visitor

Hi Charlotte,

Thank you for your response but unfortunately that doesn't work.

I agree that because Sad and Happy investments are USA, the measure [Investment Value] doesn't know which one to calculate as the current context and therefore spits out a zero for USA.

However, your solution would require I have to create a measure with each investment as a separate CALCULATE DAX. This is just a dummy set of data but in reality I have over 100 unique investments that I'm trying to categorize.

Do you have a solution using DAX that could capture all investments under USA without specifically mentioning each one in the DAX?

Community Support

Hi, @vkong6m

Is this okay? Adding a category shows the value of each USA item.

Best Regards,

Community Support Team _Charlotte

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

Frequent Visitor

@v-zhangti I really need the categories displayed as a sum because there are many rows in the dataset, and that's just one example category.

Do you have any other ideas? Thank you