cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

AvgSales Measure causes to create a cross join of the Dimension columns

When i use a table visual and have the avg measure with the Category and City dimension, it creates a cross join to show all possible combinations of the dimensions which is causing an issue.

 

Does any one know why this happens and how to avoid this ?

dimension_crossjoin.jpg
Any help is appreciated.

2 REPLIES 2
Highlighted
Super User I
Super User I

Re: AvgSales Measure causes to create a cross join of the Dimension columns

Hi @sd782

 

If possible please share your data and the final output expected on one drive or google drive and share the link to find a solution.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Highlighted
Frequent Visitor

Re: AvgSales Measure causes to create a cross join of the Dimension columns

Hi @CheenuSing

 

Please find the pbix file located at: https://1drv.ms/u/s!AnD3xad1CmGkaf1PY6XvyNtDClE

Let me know if you are able to access it.

 

What i am trying to create is a table visual which has the following columns:

City | Category | TotalSales(Measure) | AvgSalesByCategory(Measure)
 

table_visual.jpgIn the first 3 columns, i want to see the Total Sales amount for every distinct combination of City and Category and i want the 4th column to show me the Average Sales By All Categories (which would be a constant value and should repeat on all rows).

 

But what i currently observe is that i do not get an inner join of City and Category columns on the table visual, but a cross join with all the possible combinations of City-Category and Blanks for TotalSales for the wrong combinations.

 

Please let me know if you need more info.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors