cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mcray11 Regular Visitor
Regular Visitor

Help with a table visualization/DAX Query

Hi All-

I'm not every experienced with Bi and am hoping someone can help me create a useful table visualization. 

I have a set of data across a few tables, and I'm trying to display the per month summary across a few order numbers.

However I can't get my table to display the monthly summary for each order number. Instead, it repeats themin a series.

I've attached a screenshot there. I'm trying to get to a point where there is one line for every month/order number, that summarizes the other columns.

BI issue.JPG 

 

If I were in excel, I might also try this way--combining a VLookup with a Sum function to capture the sum of all entries for one site in one month and display the total.  Basically, adding a column to the below report pulling from another table:

 

 

Capture.JPG

 

thanks in advance!

 

9 REPLIES 9
Habib Established Member
Established Member

Re: Help with a table visualization/DAX Query

Can you please check the Default Summarization of your last three columns?

mcray11 Regular Visitor
Regular Visitor

Re: Help with a table visualization/DAX Query

I believe they are set to SUM, however I've also tried selecting "Don't Summarize", and it still does not resolve the issue. 

CahabaData New Contributor
New Contributor

Re: Help with a table visualization/DAX Query

In your data table (not table visual) you need to review each column and assure that the column/field is the appropriate type.  The symptoms you have are consistent with what one thinks is a number field but is actually in PBI as a text field.  So you want to change/correct every field/column to be either text or number as they should.

 

Then in your table visual every thing should be able to be made to work.  Be sure to select Unsummarize for the Year assuming it is a whole number, and potentially the same thing for the Order # unless that is text.

 

 

 

www.CahabaData.com
mcray11 Regular Visitor
Regular Visitor

Re: Help with a table visualization/DAX Query

Thank you for the suggestion. I've checked the data types and they are all correct, either whole number, decimal number or date.

I'm still not able to display the proper Val in RC for that month/Order number-only a total-in both matrix and table visuals.

I've also reviewed the relationships and the tables are connected via two other connecting tables. Could that be an issue? I'm not getting an error but am not sure what else could be wrong.

CahabaData New Contributor
New Contributor

Re: Help with a table visualization/DAX Query

Val in RC would be the last measure to set up;  leaving that out you have sum of Vehicles & Services by single month line okay?

 

What is the Val RC formula?

www.CahabaData.com
Microsoft v-haibl-msft
Microsoft

Re: Help with a table visualization/DAX Query

@mcray11

 

Could you please provide the relationship of these tables? And the answers to CahabaData’s questions are also needed.

 

Best Regards,

Herbert

Highlighted
mcray11 Regular Visitor
Regular Visitor

Re: Help with a table visualization/DAX Query

That is correct, the table displays correctly with the exception of the Val in RC column.

 

That column is not actually a calculated column, it is a sum of all values of one data type (selected via filter) from another column.

mcray11 Regular Visitor
Regular Visitor

Re: Help with a table visualization/DAX Query

Hi Herbert,

 

There are two intermediatry tables connecting the two data sources for this visualization. 

Site>Locations>Order Numbers>Val in RC data

 

Thanks for any suggestions!

Microsoft v-haibl-msft
Microsoft

Re: Help with a table visualization/DAX Query

@mcray11

 

If Val in RC is not a calculated column, we should be able to get the desired result with setting TOTAL_VEHICLES and TOTAL_SERVICES to SUM and Val in RC to Don’t summarize as below.

 

Help with a table visualization DAX Query_3.jpg

 

Best Regards,

Herbert

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (5,106)