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
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
v-yuaj-msft
Community Support
Community Support

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
v-yuaj-msft
Community Support
Community Support

Hi @mohsenask ,

 

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

 

Best Regards,

Yuna

v-yuaj-msft
Community Support
Community Support

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

 

@v-yuaj-msft 

 

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.

 

@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
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.