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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Only show measure value for certain row value in Matrix

Hi All

 

I need some help with a measure I have created showing correctly for a given row value on a matrix visual. In my model I have 3 tables (Date, Reporting, and Financial). Date and Reporting are Dims and Finacial is the Fact. I have a measure called TotalExpenseAmt which is structured like this:

 

TotalExpenseAmt =
CALCULATE(SUM(Financial[Amount]),Reporting[LOSGroup] IN {"EXPENSES"})
 
One of the attributes in Reporting is LOSGroup. When I have "TOTAL EXPENSES" on the row I would like to display this measure value. Otherwise I just want to show the value for Amount. Displaying my measure shows this for all row values. How would I switch it to only show for the "TOTAL EXPENSE" row. For the highlighted value I want it to show. Also show the Detail values for Expenses such as "GP&T", "REPAIRS", etc. That's fine. But for say "CAPITAL" i want it to be blank
 
ScottC60540_0-1679763270528.png

 

 
I have the pbix file here  SampleLOSGroup.pbix
 
This seems like a simple thing but my iterations with this measure just havent been successful right now. Appreciate any help.
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@Anonymous Try this:

TotalExpenseAmt =
  VAR __LOSGroup = MAX('Reporting'[LOSGroup])
  VAR __Result = 
    IF( __LOSGroup = "TOTAL EXPENSES",
      CALCULATE(SUM('Financial[Amount']),'Reporting'[LOSGroup] IN {"EXPENSES"}),
      SUM('Financial'[Amount])
    )
RETURN
  __Result

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Thanks Greg. That appears to work. I appreciate it

Greg_Deckler
Super User
Super User

@Anonymous Try this:

TotalExpenseAmt =
  VAR __LOSGroup = MAX('Reporting'[LOSGroup])
  VAR __Result = 
    IF( __LOSGroup = "TOTAL EXPENSES",
      CALCULATE(SUM('Financial[Amount']),'Reporting'[LOSGroup] IN {"EXPENSES"}),
      SUM('Financial'[Amount])
    )
RETURN
  __Result

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler  I think my initial problem is that the relationship between the Reporting and Financial tables is "Both" (i.e. bi-directional). If we switch it to that you can see the issue:

ScottC60540_0-1679928264914.png

If I am forced to have bi-directional between these tables how can I get that measure to work?

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors