Summarize Measures from different Tables into a Single column and derived custom column from the Res

[SayeeHarsha]

Hi All,

We have 4 different Measures from 4 different Tables (Sales, Revenue, Invoice, Demand).

Our requirement is to derive a final Measure along with a Custom column that segregates the final value into 3 different categories.

Eg : Final Sum = Sales + Revenue + Invoice,

then Final Table should be  Final Sum - Demand (Table).

Categories are divided based on the final Table value as provided below for your reference : 

Please reach out for any further information. 

Thank you !!

[v-zhouwen-msft · Accepted solution]

Hi @SayeeHarsha ,

Please follow these steps:
1. The table datas and model view as follows.

2.Use the DAX expression shown to create a measure named ‘ FinalSum-Demand ’.

FinalSum-Demand =
CALCULATE (
    (
        SUM ( Sales[Column2] ) + SUM ( Revenue[Column2] )
            + SUM ( Invoice[Column2] )
    )
        - SUM ( Demand[Column2] )
)

3.Use the DAX expression shown to create a measure .

Measure = 
var _a=[FinalSum-Demand]
var _b=SWITCH(
                    TRUE(),
                        _a = 0 , "Final Sum Equal Demand",
                        _a< 0 , "Final Sum < Demand",
                        _a> 0 , "Final Sum > Demand")
return
_b

4.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@SayeeHarsha , You need to create a new measure

m1=

var _new= ([Sales]+ [Revenue]+ [Invoice])- [Demand]

return

Switch(True(),

_new >0 , "Final Sum > Demand",

_new <0 , "Final Sum < Demand",

"Final Sum equal Demand"

)

if you need slicer, do Dynamic Segmentation

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q

Power BI ABC Analysis using Window function, Dynamic Segmentation: https://youtu.be/A8mQND2xSR4