cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DataStraine
Helper I
Helper 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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!