Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
JustinDoh1
Post Prodigy
Post Prodigy

How to display Measure values inside where Total is (inside Matrix visual)

I have some calculation that I created in Measures that I would like to display where Total value is (inside Matrxi visual).

I have attached PBI file for reference.

For example (at "Actual" tab), these two are Measures that I created using Cards.

I am trying to see if there is a way to display these calculation at where regular Total is.

JustinDoh1_0-1709588050953.png

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @JustinDoh1 ,

 

Thanks @Ashish_Mathur  for your answer and sharing, I have a small addition here.

If you want to place [Skill Mix % All] in the matrix and the TOTAL value shows [Skill Mix % Total Rest], you can consider creating measure, using the IF() + HASONEVALUE() function, to determine if it's a Total or not, and if it is, you can do the calculations according to your own needs, such as Average. Sum and so on. Because measure is incorrect in Total is a relatively common scenario, measure follows the context of the "Total" row and is calculated in that context. As a result, measures used in the columns of a table visualization may have unexpected values in the "Total" column.

Measure =
IF(
    HASONEVALUE('vw_Ocuupancy'[Provider_Name]),[Skill Mix % All],[Skill Mix % Total Rest])
Measure 2 =
IF(
    HASONEVALUE('vw_Ocuupancy'[Provider_Name]),[Occupancy % All],[Occupancy % Total Rest])

vyangliumsft_0-1709621406227.png

 

Best Regards,

Liu Yang

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

View solution in original post

4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

Hi  @JustinDoh1 ,

 

Thanks @Ashish_Mathur  for your answer and sharing, I have a small addition here.

If you want to place [Skill Mix % All] in the matrix and the TOTAL value shows [Skill Mix % Total Rest], you can consider creating measure, using the IF() + HASONEVALUE() function, to determine if it's a Total or not, and if it is, you can do the calculations according to your own needs, such as Average. Sum and so on. Because measure is incorrect in Total is a relatively common scenario, measure follows the context of the "Total" row and is calculated in that context. As a result, measures used in the columns of a table visualization may have unexpected values in the "Total" column.

Measure =
IF(
    HASONEVALUE('vw_Ocuupancy'[Provider_Name]),[Skill Mix % All],[Skill Mix % Total Rest])
Measure 2 =
IF(
    HASONEVALUE('vw_Ocuupancy'[Provider_Name]),[Occupancy % All],[Occupancy % Total Rest])

vyangliumsft_0-1709621406227.png

 

Best Regards,

Liu Yang

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

@v-yangliu-msft 

Thank you for your help again.

Can I ask you one more question?

Your DAX formula worked for visuals in "Actual" and "Budget" tabs, but I am not sure how to make it work in "Variance" tab:

JustinDoh1_0-1709763974604.png

I have uploaded my latest PBI file here:

Do these measures not work because Variance is populated by calculation, not by its own Fact table?

Thanks. 

 

 

 

@v-yangliu-msft 

This is brilliant. I will dig into this approach. Thank you!

Ashish_Mathur
Super User
Super User

You had dragged the wrong measure to the Table visual.  Drag the ones that you have to the card visuals.

Hope this helps.

Ashish_Mathur_0-1709595317835.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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