Forum Discussion

gladies123's avatar
gladies123
Icon for Helper I rankHelper I
6 years ago
Solved

Reg - To display a value for once from multiple values

Hi,

 

 

I am facing an issue regarding different 3 customers mapping in a similar project and their revenue also same .....so the output has to display like revenue should display once  and othere revenues have to be diplayed as per different sub project. herewith i attached a image for your reference.

 

Thanks in Advance

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi gladies123 

    Add a index column from power query, then create a measure

    Measure 2 =
VAR lastindex =
    CALCULATE (
        MAX ( 'Table 3'[Index] ),
        ALLEXCEPT ( 'Table 3', 'Table 3'[Sub Project], 'Table 3'[Revenue] )
    )
RETURN
    CALCULATE (
        SUM ( 'Table 3'[Revenue] ),
        FILTER ( 'Table 3', 'Table 3'[Index] = lastindex )
    )

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    What is the logic to decide which Customer shows the value for the shared sub projects?

    Regards,

    Pat

     

    • gladies123's avatar
      gladies123
      Icon for Helper I rankHelper I
      6 years ago

      Actually there is no logic  and no order in that either 1 customer has to display the value 

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi gladies123 

    Add a index column from power query, then create a measure

    Measure 2 =
VAR lastindex =
    CALCULATE (
        MAX ( 'Table 3'[Index] ),
        ALLEXCEPT ( 'Table 3', 'Table 3'[Sub Project], 'Table 3'[Revenue] )
    )
RETURN
    CALCULATE (
        SUM ( 'Table 3'[Revenue] ),
        FILTER ( 'Table 3', 'Table 3'[Index] = lastindex )
    )

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.