Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Bins in histogram visual

I have the data in the following format and I need to find the sum of amount for each type and subtract the sum of amount for base from it. 

Then, I have to make a histogram with the bins based on the outcome of this mathematical function and plot it against the number of occurrences on the y axis.

I don't know how to go about it. Kindly help.

Need Help
Show and Tell
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    I create a new table

    Table 2 =
VAR Table3 =
    SELECTCOLUMNS (
        FILTER (
            SUMMARIZE ( 'Table', 'Table'[type], "TOTAL AMOUNT", SUM ( 'Table'[amount] ) ),
            [type] = "BASE"
        ),
        "type2", [type],
        "TOTAL AMOUNT2", [TOTAL AMOUNT]
    )
RETURN
    CROSSJOIN (
        FILTER (
            SUMMARIZE (
                'Table',
                'Table'[type],
                "TOTAL AMOUNT", SUM ( 'Table'[amount] ),
                "count", COUNT ( 'Table'[type] )
            ),
            [type] <> "BASE"
        ),
        Table3
    )

    then add columns in the column chart.

     

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

5 Replies

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

    Hi Anonymous 

    I can get the result of the following:

    "the sum of amount for each type and subtract the sum of amount for base"

    But I'm not clear how to get the "average count".

    The result above is from a calculated table, if you can accept this method, please let me know how to create the "average count".

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Maggie,

       

      Ignore the table 'average table'. I had made it just to point out the count in each bin.

      I wanted to know how do i make these bins in the histogram and display the data.

      Now that we have got the result of the subtraction in the "Column", How do i display they count on the histogram and make those bins?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

       Also, can you please post the entire DAX expression for the table?

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

        Hi Anonymous 

        I create a new table

        Table 2 =
VAR Table3 =
    SELECTCOLUMNS (
        FILTER (
            SUMMARIZE ( 'Table', 'Table'[type], "TOTAL AMOUNT", SUM ( 'Table'[amount] ) ),
            [type] = "BASE"
        ),
        "type2", [type],
        "TOTAL AMOUNT2", [TOTAL AMOUNT]
    )
RETURN
    CROSSJOIN (
        FILTER (
            SUMMARIZE (
                'Table',
                'Table'[type],
                "TOTAL AMOUNT", SUM ( 'Table'[amount] ),
                "count", COUNT ( 'Table'[type] )
            ),
            [type] <> "BASE"
        ),
        Table3
    )

        then add columns in the column chart.

         

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