Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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.
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.
This does work. Thank you!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |