Forum Discussion

stanislav_dugas's avatar
stanislav_dugas
Helper III
5 years ago
Solved

Impossible mapping

Hey Guys,

 

I am a bit strugling with figuring out if my issues is even solvable. Let me try to explain it.

I have a dataset where I have IDs that have a certain mapping to them splitting then on 3 groups Core/Solution/Transaction.

I also have a column giving me a contribution for solution. What does it mean is that if ID has value 20 and contribution 10% then 2 is part of solution and remaining is part of core. Only solution can have this kind of contribution. The Contribution values are only whole numbers like 10%,25%,50%,100%.

What i am trying to do is to create a value calculation that would give me the values after applying the contribution for solution and put the remaining delta under the core so in reality it would somehow split the ID into 2 portion one would be core and one Solution without actually creating new row in my dataset as part of a meassure.

 
IDValueContribution %MappingValue after aplying contributionDelta to be added to CoreExpected Result
1100Core10028
22010Solution2182
3300Transaction30030

 

Is this even possible?

 

Thanks for any help.

Stan

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi , stanislav_dugas 

    PowerBI doesn't support to  add additional rows by using measure.

    You need to create a new calculated table as below:

     

     

    Table 2 =
VAR tab =
    ADDCOLUMNS (
        FILTER (
        SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[Delta To be added to Core] ),
        'Table'[Delta To be added to Core] <> 0
    ),
        "Value", "",
        "Solution Contribution %", "0",
        "Mapping", "Core",
        "Value After Appling contribution %", ""
    )
RETURN
    UNION (
        SUMMARIZE (
            'Table',
            'Table'[ID],
            'Table'[Delta To be added to Core],
            'Table'[Value],
            'Table'[Solution Contribution %],
            'Table'[Mapping],
            'Table'[Value After Applying contribution %]
        ),
        tab
    )

     

     



    You can  also take a try the  function "append queries as new" in advanced editor.


    Please check my sample file for more details.


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

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi stanislav_dugas 

     

    Can you explin how you arrvied at the values Expected result in the image posted ?

    What does ID refer to. Does each ID have 3 different possible values , Core/ Solution / Transaction.

     

    If you can share the pbix it will help to understand better.

     

    Cheers

     

    CheenuSing

    • stanislav_dugas's avatar
      stanislav_dugas
      Helper III
      5 years ago

      Hi CheenuSing,

       

      Each ID has only 1 value either its Core or Solution or Transaction.

       

      Expected value is combination of Value after aplying contribution column and the Delta column where the delta needs to be allocated towards the Core part of the business.

      So 1st ID= Value after contribution + Delta from Solution

      2nd ID = Value after Contribution

      3rd ID = Value after Contribution

       

      Hope this helps.

       

      Cant share the Pbix file since the data are confidential.

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

        Hiļ¼Œ stanislav_dugas 
        The table currently contains three rows of  sample data.

        If possible ,can you share more sample data to help us understand the problem?

         

        Best Regards,
        Community Support Team _ Eason

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

    Hi , stanislav_dugas 

    PowerBI doesn't support to  add additional rows by using measure.

    You need to create a new calculated table as below:

     

     

    Table 2 =
VAR tab =
    ADDCOLUMNS (
        FILTER (
        SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[Delta To be added to Core] ),
        'Table'[Delta To be added to Core] <> 0
    ),
        "Value", "",
        "Solution Contribution %", "0",
        "Mapping", "Core",
        "Value After Appling contribution %", ""
    )
RETURN
    UNION (
        SUMMARIZE (
            'Table',
            'Table'[ID],
            'Table'[Delta To be added to Core],
            'Table'[Value],
            'Table'[Solution Contribution %],
            'Table'[Mapping],
            'Table'[Value After Applying contribution %]
        ),
        tab
    )

     

     



    You can  also take a try the  function "append queries as new" in advanced editor.


    Please check my sample file for more details.


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