Bins in histogram visual

[Anonymous]

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.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-juanli-msft]

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]

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

[v-juanli-msft · Accepted solution]

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.

[Anonymous]

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?