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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
mohsenask
Helper II
Helper II

Matrix row shows different values to table

Hi

 

Im struggling to figure out this problem. Ive worked with Matrix before but this is driving me crazy. I have a table with following columns
Date

Total Qty - quantity of cabins available
Total Sold - quantity of cabins sold

When using a simple Table visual it shows the correct Total Qty available which is max of 9. But when i use the Matrix as a visual it seems to show a different total available rooms for certain dates. The total rooms cannot be more than 9.

 

Screenshot 2021-03-24 at 18.50.01.png

Just FYI Ive created three measures as below to be able to show items with no data. For e.g.

 

MS_Actual_TotalQty =
IF (
CALCULATE ( SUM ( usp_GetAllocationsalesDetails_PI[TotalQty] ) ) = BLANK (),
0,
CALCULATE ( SUM ( usp_GetAllocationsalesDetails_PI[TotalQty] ) )
)
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @mohsenask ,

 

However, I didn't see the error you described in the matrix visual based on your attached .pbix file.

v-yuaj-msft_0-1616748022364.png

 

Best Regards,

Yuna

 

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Hi @mohsenask ,

 

If your problem has been solved, Could you kindly Accept it as the solution? 😉

 

Best Regards,

Yuna

Anonymous
Not applicable

Hi @mohsenask ,

 

However, I didn't see the error you described in the matrix visual based on your attached .pbix file.

v-yuaj-msft_0-1616748022364.png

 

Best Regards,

Yuna

 

@Anonymous 

 

I fixed the problem - the file on dropbox is updated one. Ill move it. Thanks however!!

Hi Can you please share hopw you solved this. Thanks

amitchandak
Super User
Super User

@mohsenask , Try this

MS_Actual_TotalQty =
IF (
isblank(CALCULATE ( SUM ( usp_GetAllocationsalesDetails_PI[TotalQty] ) )),
0,
CALCULATE ( SUM ( usp_GetAllocationsalesDetails_PI[TotalQty] ) )
)

 

or

 

SUM ( usp_GetAllocationsalesDetails_PI[TotalQty] )+0

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

 

@amitchandak - Thanks for your reply

 

Sorry tried to paste the table here but it gives me some weird HTML error. IT says removed the invalid HTML and try submitting again but doesnt work.

Tried uploading csv or xls - didnt allow.

 

The below extract from the data table shows totalQty as max 9

Screenshot 2021-03-24 at 19.39.37.png

in a simple table visual the rows show correct TotalQty as 9

 

Screenshot 2021-03-24 at 19.32.00.png

 

However in the matrix its shows TotalQty as 16 for certain dates

Screenshot 2021-03-24 at 18.50.01.png

 

@amitchandak - any help??? 

 

Ive attached a test pbix file - https://www.dropbox.com/s/59c5luuqbby2wxg/BG_MIS_test.pbix?dl=0 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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