Skip to main content
cancel
Showing results for 
Search instead 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

Reply
vkong6m
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. 

vkong6m_0-1656563755389.png

 

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

 

Excel and pbix here: https://drive.google.com/drive/folders/11WZKuWiNRz7vYAKmY0SP0gRwj8-qWJjo?usp=sharing

 

Many thanks in advance!

5 REPLIES 5
vkong6m
Frequent Visitor

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

v-zhangti
Community Support
Community Support

Hi, @vkong6m 

 

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

vzhangti_0-1656927688570.pngvzhangti_1-1656927701624.png

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" )
    )

vzhangti_0-1656928198768.png

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.

 

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?


Hi, @vkong6m 

 

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

vzhangti_0-1657247572890.pngvzhangti_1-1657247592446.png

 

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.

 

@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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.