topic Re: Calculated Table in Power Query

Calculated Table

Anonymous — Fri, 17 Sep 2021 20:35:09 GMT

Hello Trying to get a top n and others calculation going on for some visuals I created a calculated table to get distinct names from a client table using this

Customers Plus Others =

Union(

VALUES(ClientType[Client]), --get table of all customer names

Row( "Customers", "Others") --get table of Others

)

Table shows up correctly on visual no problem , now when I try to add my total tonnage measure, the same amount gets repeated

this is the measure for total tonnage:

Total Tons = SUM('Sales Values'[Tonnage])

How can i fix this? the client type table has a relationship with the sales values table if I use the customer names from the client Type table I have no problems the only thing I don't have a "Others"row

Re: Calculated Table

ponnusamy — Sat, 18 Sep 2021 13:52:39 GMT

Anonymous See if this works.

Table # 1

Table # 2

Table # 3 (Sale Fact)

Union Table (DAX)

Result

If this post helps, then please consider Accepting it as the solution, Give Kudos to motivate the contributors.

Re: Calculated Table

Syndicate_Admin — Sat, 18 Sep 2021 14:00:56 GMT

@cmgp01 See if this works.

Table # 1

Table # 2

Table # 3 (Sale Fact)

Union Table (DAX)

Result

Re: Calculated Table

Syndicate_Admin — Sat, 18 Sep 2021 15:01:18 GMT

@cmgp01 See if this works.

Table # 1

Table # 2

Table # 3 (Sale Fact)

Union Table (DAX)

Result

Re: Calculated Table

v-yingjl — Tue, 21 Sep 2021 01:30:16 GMT

Hi Anonymous ,

If not want to use the relationship in the previous table, you can create the calculated table like this:

Customers Plus Others = UNION('ClientType','Customers')

Create the total measure:

Total = CALCULATE ( SUM ( 'Sales Values'[Tonnage] ), FILTER ( 'Sales Values', 'Sales Values'[ID] IN DISTINCT ( 'Customers Plus Others'[ID] ) ) )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculated Table

Anonymous — Tue, 21 Sep 2021 11:48:13 GMT

This woks but not what I was looking for since I am crating a top n with an "Others" row. I found the solution by creating another measure:

Total Tons 2 = CALCULATE ( SUM ( 'Sales Values'[Tonnage] ), TREATAS ( VALUES ( 'Customers Plus Others'[Customers]), ClientType[Client] ) )

and this gives me the result I was looking for

Thanks for your help!!

Re: Calculated Table

Syndicate_Admin — Tue, 21 Sep 2021 12:01:33 GMT

This woks but not what I was looking for since I am crating a top n with an "Others" row. I found the solution by creating another measure:

Total Tons 2 = CALCULATE ( SUM ( 'Sales Values'[Tonnage] ), TREATAS ( VALUES ( 'Customers Plus Others'[Customers]), ClientType[Client] ) )

and this gives me the result I was looking for

Thanks for your help!!