Forum Discussion

DataStraine's avatar
DataStraine
Advocate I
4 years ago
Solved

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...
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

v-chenwuz-msft's avatar
v-chenwuz-msft
Community Support
4 years ago

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's avatar
    DataStraine
    Advocate I
    4 years ago

    This does work. Thank you!