cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RMV
Helper V
Helper V

combined table

Hi,

 

I'm trying to find a smarter way to create 1 table, to combine 2 types of calculation, i.e. a sum of a category & a result from a measured column.

For example:

sum of Category A = 1000 (which is a sum from the raw data)

sum of Category B = 900 (which is a sum from the raw data)

Gap = 100 (which is returned from a measured column (Gap = SUM([Category A]) + SUM([Category B])

 

The table result is suppose to look like this:

 

 TotalAverage
Category A1000110
Category B90095
Gap10015

 

Now, have 2 separate tables for Category A & Category B total & average, and another table for the Gap measured total & average.

1 ACCEPTED SOLUTION

@RMV

 

We used calculated columns to get the desired table, so using slicer here is not work as expected, unless we use calculated measure.

But using calculate measures, we will not be able to get the desired table format, the only way is to use two table visuals.

 

Best Regards,
Herbert

View solution in original post

4 REPLIES 4
v-haibl-msft
Microsoft
Microsoft

@RMV

 

In such scenario, we may need to create another table and some calculated columns as a trick.

The new created table needs to have a column which includes Category A, B, and Gap like following screenshot. Then we can use IF function to do the desired calculations.

For details, please refer to attached PBIX file.

 

combined table_1.jpgcombined table_2.jpgcombined table_3.jpg

 

Best Regards,
Herbert

 

Hi @v-haibl-msft,

 

This is awesome. I got the concept & managed to create the format.

However, I faced another challenge.

I have 2 slicers from the raw data table, i.e. Table 1 in this example, i.e. entity & month.

Need a further advise, how can I link the slicers to the new table?

@RMV

 

We used calculated columns to get the desired table, so using slicer here is not work as expected, unless we use calculated measure.

But using calculate measures, we will not be able to get the desired table format, the only way is to use two table visuals.

 

Best Regards,
Herbert

Hi @v-haibl-msft,

Thanks for the explanation.

It's not what I expected, since it took an amount of time to do the setting (for example format, interactions, etc)

But, I can live with separated tables for now Smiley Happy

Thanks again.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors