topic Re: Calculation of Sum with Filters - Which is right DAX to use? in DAX Commands and Tips

Calculation of Sum with Filters - Which is right DAX to use?

Anonymous — Tue, 22 Mar 2022 14:24:27 GMT

This is probably an easy solution for you guys but I'm rusty on my Dax.

I'm trying to create a visualization that includes one column of filtered sales data (by a field in my item master called "manufacturer") and a separate calculation that is the TOTAL of the category that still includes the data being filtered in the prior column. This is an OVERLY simplified model of my sales data, and a relatively simple output for the visualization as well. Eventually I'll bring in time intelligence, changes vs prior year etc. I've addressed this in past with slicers but the slicer then filters out all the sales data so I can't have both the manufacturer and total category sales on the same visualization.

I just need to understand what is the appropriate command to create the filtered sales columns and unfiltered category sales columns.

EXAMPLE OF DATA TABLE
Item Description	Category	Manufacturer	Week #	Sales
Item 1	Widget	Company 1	1	$25
Item 2	Cords	Company 1	1	$40
Item 3	Widget	Company 2	1	$33
Item 4	Cords	Company 2	1	$50
Item 5	Widget	Company 3	1	$18
Item 6	Cords	Company 3	1	$45
Item 7	Widget	Company 4	1	$22
Item 8	Cords	Company 4	1	$47

EXAMPLE of DESIRED VISUALIZATON

	Company 1	Category TTL (Thus should including Company 1 as part of the sum)
Widgets	$25	$98
Cords	$40	$182

Re: Calculation of Sum with Filters - Which is right DAX to use?

amitchandak — Tue, 22 Mar 2022 14:59:26 GMT

Anonymous , Unpivot category and company in power query and try

https://radacad.com/pivot-and-unpivot-with-power-bi

Re: Calculation of Sum with Filters - Which is right DAX to use?

Anonymous — Tue, 22 Mar 2022 15:01:47 GMT

Category and company are both supplemental fields in the item master. The item id# and sales come from my weekly sales data imports and then are linked in a relationship.

Re: Calculation of Sum with Filters - Which is right DAX to use?

Anonymous — Tue, 22 Mar 2022 15:16:02 GMT

sorry to clarify...

My Data Table Only Includes sales data not the deeper item description or manufacturer details:

Item Code

$ Sales

Week

My Item Master Table Includes

Item Code (Key Field)

Manufacturer

Re: Calculation of Sum with Filters - Which is right DAX to use?

Whitewater100 — Tue, 22 Mar 2022 15:19:33 GMT

Hello:

I rearranged the model a bit, to make it easier.

https://drive.google.com/file/d/1gBSzt8Ji9gQvrIweCKIB0uy8N5QLW5h4/view?usp=sharing

Re: Calculation of Sum with Filters - Which is right DAX to use?

Anonymous — Tue, 22 Mar 2022 18:19:20 GMT

This helped! Thanks.