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
rampsaladi
Frequent Visitor

How to Divide multiple rows for with another row from the same Column

Hi

 

I'm trying to create a measure where the denominator will always be same but the numerator will change. Both the Numerator and Denominator are referencing the same column. Below is an example. I need to get to $_Share column. Would like to know how to get to that using DAX

 

CustomerTime PerriodProduct $$_Share 
ABC1/25/2017prod 11001100/100
ABC1/25/2017prod 2750.7575/100
ABC1/25/2017prod 3550.5555/100
ABC1/25/2017prod 4600.660/100
DEF12/22/2017prod 11251125/125
DEF12/22/2017prod 21000.8100/125
DEF12/22/2017prod 3750.675/125
DEF12/22/2017prod 4600.4860/125

        

Any Help would greatly be appreciated

1 ACCEPTED SOLUTION

@rampsaladi

 

Seems your logic is what @parry2k has stated.

 

As a calculated column, try this

 

Column =
DIVIDE (
    TableName[$],
    CALCULATE (
        SUM ( TableName[$] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Customer] ),
            TableName[Product] = "prod 1"
        )
    )
)

As a MEASURE

 

MEASURE =
DIVIDE (
    SUM ( TableName[$] ),
    CALCULATE (
        SUM ( TableName[$] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Customer] ),
            TableName[Product] = "prod 1"
        )
    )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
parry2k
Super User
Super User

what is logic which decides the value denominator? Seems like it is $ value of 1st row of each customer where product = "Prod1" or something else?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@rampsaladi

 

Seems your logic is what @parry2k has stated.

 

As a calculated column, try this

 

Column =
DIVIDE (
    TableName[$],
    CALCULATE (
        SUM ( TableName[$] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Customer] ),
            TableName[Product] = "prod 1"
        )
    )
)

As a MEASURE

 

MEASURE =
DIVIDE (
    SUM ( TableName[$] ),
    CALCULATE (
        SUM ( TableName[$] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Customer] ),
            TableName[Product] = "prod 1"
        )
    )
)

Regards
Zubair

Please try my custom visuals

This is great It worked wonders  thanks 

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.