Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Level Of Detail Inquiry

I have two ORGCODE, the CDW and the SGW,

 

How do i get the FIXED Level of detail value for the SEGMENT in multiplying the;


Fixed Segments = Cost in ORGCODE = SGW multiply by the Qty Rate in ORGCODE = CDW. 

 

In Tableau : FIXED [SEGMENTS]: MIN(IF [ORGCODE]="SGW" THEN [ITEM_COST] END)} * [QUANTITY_RATE])

 

How about in Power BI?

 

 

Need Help
Show and Tell
Tips and Tricks
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi, Anonymous 

    I am not familiar with formulas in Tableau.

    Did you want to group by Segments in powerbi to get the minimum  value of [ITEM_COST]  * [QUANTITY_RATE]) ?

    Please try formula as nelow:

    M_Cost = 
CALCULATE (
    MAX ( 'Table'[Cost] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[ORGCODE] = "SGW"
            && 'Table'[SEGMENTS] = MAX ( 'Table'[SEGMENTS] )
    )
)

    M_Rate = 
CALCULATE (
    MAX ( 'Table'[Rate] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[ORGCODE] = "CDW"
            && 'Table'[SEGMENTS] = MAX ( 'Table'[SEGMENTS] )
    )
)

    FIXED Level of detail value = 
[M_Cost]*[M_Rate]

    Please check my sample file for more details.

     

    If doesn't meet your requirement , please explain the formula in detail.It will be better if you can share you expected in excel.

     

    Best Regards,
    Community Support Team _ Eason

1 Reply

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    5 years ago

    Hi, Anonymous 

    I am not familiar with formulas in Tableau.

    Did you want to group by Segments in powerbi to get the minimum  value of [ITEM_COST]  * [QUANTITY_RATE]) ?

    Please try formula as nelow:

    M_Cost = 
CALCULATE (
    MAX ( 'Table'[Cost] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[ORGCODE] = "SGW"
            && 'Table'[SEGMENTS] = MAX ( 'Table'[SEGMENTS] )
    )
)

    M_Rate = 
CALCULATE (
    MAX ( 'Table'[Rate] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[ORGCODE] = "CDW"
            && 'Table'[SEGMENTS] = MAX ( 'Table'[SEGMENTS] )
    )
)

    FIXED Level of detail value = 
[M_Cost]*[M_Rate]

    Please check my sample file for more details.

     

    If doesn't meet your requirement , please explain the formula in detail.It will be better if you can share you expected in excel.

     

    Best Regards,
    Community Support Team _ Eason