Forum Discussion

Fidzi8's avatar
Fidzi8
Icon for Helper V rankHelper V
6 years ago

% share in column

Hi,
I would like to calculate %QTY share for haulier on the more destinations.

First table shows %QTY when I filter the destination (from-to). Haulier CEMEX has 20,11%.

Second table shows %QTY when i filter haulier CEMEX. Same destination has 7,42%.

Can I calculate this value? I could create new column or new table. 

 

Than you
Ondřej

Need Help
Tips and Tricks

4 Replies

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

    Hi Fidzi8 ,

    Acutually measure can achieve but it seems to be static in this way:

    Measure =
VAR _h =
    SELECTEDVALUE ( 'Table'[Haulier] )
VAR _c =
    SELECTEDVALUE ( 'Table'[Customer] )
RETURN
    IF (
        _h = "CEMEX"
            && _c = "Beto Hodo",
        DIVIDE (
            CALCULATE (
                SUM ( 'Table'[Qty] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Haulier] = "CEMEX"
                        && 'Table'[Customer] = "Beto Hodo"
                )
            ),
            CALCULATE (
                SUM ( 'Table'[Qty] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Customer] = "Beto Hodo" )
            )
        ),
        IF (
            _h = "FCC"
                && _c = "Beto Hodo",
            DIVIDE (
                CALCULATE (
                    SUM ( 'Table'[Qty] ),
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Haulier] = "FCC"
                            && 'Table'[Customer] = "Beto Hodo"
                    )
                ),
                CALCULATE (
                    SUM ( 'Table'[Qty] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[Customer] = "Beto Hodo" )
                )
            ),
            DIVIDE (
                SUM ( 'Table'[Qty] ),
                CALCULATE ( SUM ( 'Table'[Qty] ), ALLSELECTED ( 'Table' ) )
            )
        )
    )

     

    Sample file is attached, please check it: % share in column.pbix 

     

    Best Regards,
    Yingjie Li

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

    • Fidzi8's avatar
      Fidzi8
      Icon for Helper V rankHelper V
      6 years ago

      Hi v-yingjl ,

      this is great. 
      What should I do when I have 100 customers and 20 hauliers.


      1. Must I do for each haulier function IF?
      2. Couldn´t I use column - code haulier?

      3. When every week I will update database. Must I add manually new hauliers or customers?

      Thank you
      Ondřej

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Fidzi8 

    so, what result do you need?

    I see completely different logic in two pictures

    • Fidzi8's avatar
      Fidzi8
      Icon for Helper V rankHelper V
      6 years ago

      Hi,

      I will to try to describe my intention.

      When I filter table by Destination code - I will get % share Delivery quantity for Destination and Haulier ( CEMEX 20%)

      I want filter table by Haulier code (e.g. 4311) and I would see all destinations this haulier and real % share Delivery quantity.

      Now I see 7,24% but right is 2O,11%.

       

      Thank you
      Ondřej