Forum Discussion

srpeters's avatar
srpeters
Icon for Helper II rankHelper II
2 years ago
Solved

DAX function not working in power query column

I have a column that displays what generation people fall under based off of their birth year. I want to also display the total percentage of those who fall into this catagory. I made this DAX to do this: 

 

Baby Bomer = CONCATENATE(DIVIDE(calculate(count(Table[B]),'Table'[B].[Year] >= 1946 && 'Table'[B].[Year] <= 1964), (COUNT(Table[isHere])))*100,"%") 
 
This DAX gives the correct percentage when in a card, but when I contatinate this to the baby boomer part of the generation column it just displays 100%. I beleive this is because all of the conditional statement being satisfied 100% of the time, but how would I add the correct value without it being affected by the generation columns' conditional? 
  • 123abc's avatar
    123abc
    2 years ago

    It seems like you're trying to calculate the percentage of Baby Boomers within your dataset. However, the DAX expression you provided seems a bit off for achieving that within Power Query, as it's typically used in Power BI or Excel.

    To calculate the percentage of Baby Boomers within your dataset in Power Query, you can follow these steps:

    1. Create a new column in your Power Query editor.
    2. Use the following M language code to calculate the percentage of Baby Boomers:

    = if [Year] >= 1946 and [Year] <= 1964 then 1 else 0

    This code will create a binary column where 1 represents individuals who fall under the Baby Boomer category based on the birth year and 0 represents others.

    1. Once you have this binary column, you can then calculate the percentage of Baby Boomers using the following steps:
    • Go to the Home tab in the Power Query Editor.
    • Click on the "Group By" option.
    • Select the column containing the binary values (1s and 0s).
    • Choose to group by the column.
    • Add an aggregation operation to calculate the percentage (you can choose to sum the binary values and then divide by the total count).

    This should give you the correct percentage of Baby Boomers within your dataset without affecting the conditional statement used for the generation column. Make sure to adjust the column names and conditions according to your actual dataset structure.

     

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

     

    In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

2 Replies

  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    2 years ago

    It seems like you're trying to calculate the percentage of Baby Boomers within your dataset. However, the DAX expression you provided seems a bit off for achieving that within Power Query, as it's typically used in Power BI or Excel.

    To calculate the percentage of Baby Boomers within your dataset in Power Query, you can follow these steps:

    1. Create a new column in your Power Query editor.
    2. Use the following M language code to calculate the percentage of Baby Boomers:

    = if [Year] >= 1946 and [Year] <= 1964 then 1 else 0

    This code will create a binary column where 1 represents individuals who fall under the Baby Boomer category based on the birth year and 0 represents others.

    1. Once you have this binary column, you can then calculate the percentage of Baby Boomers using the following steps:
    • Go to the Home tab in the Power Query Editor.
    • Click on the "Group By" option.
    • Select the column containing the binary values (1s and 0s).
    • Choose to group by the column.
    • Add an aggregation operation to calculate the percentage (you can choose to sum the binary values and then divide by the total count).

    This should give you the correct percentage of Baby Boomers within your dataset without affecting the conditional statement used for the generation column. Make sure to adjust the column names and conditions according to your actual dataset structure.

     

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

     

    In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

    • srpeters's avatar
      srpeters
      Icon for Helper II rankHelper II
      2 years ago

      123abc  This solution works for the baby boomer generation and that is why I accepted it as the solution, however, I was wondering if you knew of an alternative approach that did not require me to make a seperate columb for each generation? The goal is to have my bar graph display the percentage of each generation next to their x axis lables.

       

      Thank you