Create a single view table with data based on different conditions?

[DataStraine]

I'm in a bit of a quandary and I'm hoping that someone here can help me.  In the dummy data table below, "Month-Year" comes from a calendar table and all the other data comes from a single fact table. The issue that I am having is that I need to allow the user to select which level 3 they care about for quantity, but still have the total value for the associated level 2 value all on the same view table on a month-by-month basis.

For example, if the user only wanted to look at Product 2, then only product 2 would display on the table. They then want to pick the hardware2 to get the selectable quantity. The table doesn't get filtered further for this, it is just used to get the values to put into the column noted in the dummy table. Under these conditions the total value would be 28487, the quantity selected would be 45, and the total value/quantity would 633.04 for each row in 20-NOV but the other data would remain visible.


How do I go about this? I've tried doing all except, calculate with selected values and other methods but none seemed to work

Month-Year	Level 2	Level 3	Quantity	Value	Slicer Selected Quantity Based on Level 3 & Month	Slicer Selected Total Value based on Level 2 & Month	Slicer Selected Total Value/ Slicer Selected Quantity (Monthly)
20-Nov	Product1	Accessories1	11	$52.27
20-Nov	Product1	Hardware1	14	$4,386.06
20-Nov	Product1	License1	18	$2,102.97
20-Nov	Product1	Package1	16	$16,248.02
20-Dec	Product1	Accessories1	64	$371.47
20-Dec	Product1	Hardware1	75	$23,156.64
20-Dec	Product1	License1	104	$15,187.89
20-Dec	Product1	Package1	74	$74,844.02
20-Dec	Product1	Upgrade1	11	$4,754.98
21-Jan	Product1	Accessories1	10	$44.49
21-Jan	Product1	Hardware1	8	$2,271.91
21-Jan	Product1	License1	12	$1,154.88
21-Jan	Product1	Package1	8	$7,342.93
21-Jan	Product1	Upgrade1	1	$34.13
21-Feb	Product1	Accessories1	34	$141.41
21-Feb	Product1	Hardware1	11	$3,140.02
21-Feb	Product1	License1	16	$1,705.65
21-Feb	Product1	Package1	13	$15,369.65
21-Mar	Product1	Accessories1	61	$585.78
21-Mar	Product1	Hardware1	58	$17,390.59
21-Mar	Product1	License1	76	$9,531.14
21-Mar	Product1	Package1	57	$66,180.54
21-Mar	Product1	Upgrade1	3	$1,290.83
20-Nov	Product2	Accessories2	35	$65.34
20-Nov	Product2	Hardware2	45	$5,482.58
20-Nov	Product2	License2	59	$2,628.72
20-Nov	Product2	Package2	50	$20,310.02
20-Dec	Product2	Accessories2	208	$464.33
20-Dec	Product2	Hardware2	245	$28,945.80
20-Dec	Product2	License2	336	$18,984.86
20-Dec	Product2	Package2	240	$93,555.03
20-Dec	Product2	Upgrade2	34	$5,943.73
21-Jan	Product2	Accessories2	33	$55.62
21-Jan	Product2	Hardware2	26	$2,839.89
21-Jan	Product2	License2	38	$1,443.60
21-Jan	Product2	Package2	25	$9,178.66
21-Jan	Product2	Upgrade2	3	$42.66
21-Feb	Product2	Accessories2	111	$176.76
21-Feb	Product2	Hardware2	34	$3,925.03
21-Feb	Product2	License2	52	$2,132.06
21-Feb	Product2	Package2	41	$19,212.06
21-Mar	Product2	Accessories2	199	$732.22
21-Mar	Product2	Hardware2	187	$21,738.24
21-Mar	Product2	License2	247	$11,913.93
21-Mar	Product2	Package2	186	$82,725.67
21-Mar	Product2	Upgrade2	9	$1,613.53

[v-chenwuz-msft · Accepted solution]

Hi @DataStraine ,

Try these measure:

Slicer Selected Quantity Based on Level 3 & Month = SUM('Table'[Quantity])

Slicer Selected Total Value based on Level 2 & Month =
CALCULATE(
    SUM( 'Table'[Value] ),
    FILTER( ALL( 'Table' ), [Level 2] = SELECTEDVALUE( 'Table'[Level 2] ) ),
    FILTER(
        ALL( 'Calendar' ),
        [Month-Year] = SELECTEDVALUE( 'Calendar'[Month-Year] )
    )
)

Slicer Selected Total Value/Slicer Selected Quantity (Monthly) =
DIVIDE(
    [Slicer Selected Total Value based on Level 2 & Month],
    [Slicer Selected Quantity Based on Level 3 & Month]
)

The result:

I put my pbix file in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataStraine]

This does work. Thank you!