Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Need Help Consolidating my 3 Measures into 1!

Hi I was able to create these 3 measures: 

 

Total = (CALCULATE('Sales'[x], 'Customers'[Markets]="All of US"))

 

 

CustomSales = (CALCULATE(SWITCH(

SELECTEDVALUE('Customers'[Total US Markets]),

"Retailer 4",

CALCULATE([x],'ITEM Control'[Control Item] = "N"),

CALCULATE([$]))))

 

 

Division Equation =
SUMX (
VALUES ( 'Customers'[Markets]),
DIVIDE (
CustomSales,
CALCULATE ( Total, All('Customers'[Markets] )
) * 100
))

 

 

 

But I want to improve my DAX and just create one measure to have everything involved. 

 

What the 'Total' Measure is doing is creating my dominator.

 

I have a bunch of Customers and one of them has this special use case where I need to use a switch statement to get the information for that retailer, this is essentially my numerator (there's 30 different retailers, the switch is only needed for retailer 4) 

 

CustomSales = (CALCULATE(SWITCH(

SELECTEDVALUE('Customers'[Total US Markets]),

"Retailer 4",

CALCULATE([x],'ITEM Control'[Control Item] = "N"),

CALCULATE([$]))))

 

 

Now I have this measure doing this Retailer/Total * 100, for each specific retailer 

 

Division Equation =
SUMX (
VALUES ( 'Customers'[Markets]),
DIVIDE (
CustomSales,
CALCULATE ( Total, All('Customers'[Markets] )
) * 100
))

 

Hoping to get all 3 into just one DAX measure I can copy and paste and send to friends.

 

 

3 REPLIES 3
AlexisOlson
Super User
Super User

DAX is often easier to understand if you break it into multiple logical components like you have. But if you need to put it all in a single measure, then you should be able to substitute in the Total and CustomSales into Division Equation.

 

Division Equation =
SUMX (
    VALUES ( 'Customers'[Markets] ),
    DIVIDE (
        CALCULATE (
            SWITCH (
                SELECTEDVALUE ( 'Customers'[Total US Markets] ),
                "Retailer 4", CALCULATE ( [x], 'ITEM Control'[Control Item] = "N" ),
                CALCULATE ( [$] )
            )
        ),
        CALCULATE ( [x], 'Customers'[Markets] = "All of US" )
    ) * 100
)

 

I've done a bit of cleanup in the above. Straight substitution would look like this:

Division Equation =
SUMX (
    VALUES ( 'Customers'[Markets] ),
    DIVIDE (
        (
            CALCULATE (
                SWITCH (
                    SELECTEDVALUE ( 'Customers'[Total US Markets] ),
                    "Retailer 4", CALCULATE ( [x], 'ITEM Control'[Control Item] = "N" ),
                    CALCULATE ( [$] )
                )
            )
        ),
        CALCULATE (
            ( CALCULATE ( 'Sales'[x], 'Customers'[Markets] = "All of US" ) ),
            ALL ( 'Customers'[Markets] )
        ) * 100
    )
)

 

Anonymous
Not applicable

Hi I meant to put x for $, at the very end, of the switch statement 

 

 

Master Dax Formula =

Total = (CALCULATE('Sales'[x], 'Customers'[Markets]="All of US"))

CustomSales = (CALCULATE(SWITCH(

SELECTEDVALUE('Customers'[Total US Markets]),

"Retailer 4",

CALCULATE([x],'ITEM Control'[Control Item] = "N"),

CALCULATE([x]))))

Division Equation =
SUMX (
VALUES ( 'Customers'[Markets]),
DIVIDE (
CustomSales,
CALCULATE ( Total, All('Customers'[Markets] )
) * 100
))

is actually my code im sorry. 

Hi @Anonymous ,

What's your expected result? Could you please provide some raw data and explain the calculation logic using some specific examples and backend scenario? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors