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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.