Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DataStraine
Advocate I
Advocate I

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

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-YearLevel 2Level 3QuantityValueSlicer Selected Quantity Based on Level 3 & MonthSlicer Selected Total Value based on Level 2 & Month

Slicer Selected Total Value/

Slicer Selected Quantity (Monthly)

 

20-NovProduct1 Accessories111$52.27   
20-NovProduct1 Hardware114$4,386.06   
20-NovProduct1 License118$2,102.97   
20-NovProduct1 Package116$16,248.02   
20-DecProduct1 Accessories164$371.47   
20-DecProduct1 Hardware175$23,156.64   
20-DecProduct1 License1104$15,187.89   
20-DecProduct1 Package174$74,844.02   
20-DecProduct1 Upgrade111$4,754.98   
21-JanProduct1 Accessories110$44.49   
21-JanProduct1 Hardware18$2,271.91   
21-JanProduct1 License112$1,154.88   
21-JanProduct1 Package18$7,342.93   
21-JanProduct1 Upgrade11$34.13   
21-FebProduct1 Accessories134$141.41   
21-FebProduct1 Hardware111$3,140.02   
21-FebProduct1 License116$1,705.65   
21-FebProduct1 Package113$15,369.65   
21-MarProduct1 Accessories161$585.78   
21-MarProduct1 Hardware158$17,390.59   
21-MarProduct1 License176$9,531.14   
21-MarProduct1 Package157$66,180.54   
21-MarProduct1 Upgrade13$1,290.83   
20-NovProduct2 Accessories235$65.34   
20-NovProduct2 Hardware245$5,482.58   
20-NovProduct2 License259$2,628.72   
20-NovProduct2 Package250$20,310.02   
20-DecProduct2 Accessories2208$464.33   
20-DecProduct2 Hardware2245$28,945.80   
20-DecProduct2 License2336$18,984.86   
20-DecProduct2 Package2240$93,555.03   
20-DecProduct2 Upgrade234$5,943.73   
21-JanProduct2 Accessories233$55.62   
21-JanProduct2 Hardware226$2,839.89   
21-JanProduct2 License238$1,443.60   
21-JanProduct2 Package225$9,178.66   
21-JanProduct2 Upgrade23$42.66   
21-FebProduct2 Accessories2111$176.76   
21-FebProduct2 Hardware234$3,925.03   
21-FebProduct2 License252$2,132.06   
21-FebProduct2 Package241$19,212.06   
21-MarProduct2 Accessories2199$732.22   
21-MarProduct2 Hardware2187$21,738.24   
21-MarProduct2 License2247$11,913.93   
21-MarProduct2 Package2186$82,725.67   
21-MarProduct2 Upgrade29$1,613.53   
1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

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:

vchenwuzmsft_1-1637216962800.png

 

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.

View solution in original post

2 REPLIES 2
v-chenwuz-msft
Community Support
Community Support

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:

vchenwuzmsft_1-1637216962800.png

 

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! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.