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 t...
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.
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.