Forum Discussion

cduggan's avatar
cduggan
Regular Visitor
3 years ago
Solved

Creating chart for Count by Count of Count

Hello I have been struggling to create a chart the way I wanted to for a while now and thought I would ask for help as I can't find any concrete answers.

I have a table formatted as follows

DateNameValidPrice
1/1/2023JohnYes$1
1/1/2023JohnNo$4
1/1/2023JaneYes$1
1/2/2023JohnYes$3
1/2/2023GaryNo$2

The chart I am looking to display has two slicers that need to apply to it, Date and Valid (I have already created a calendar table and linked it appropriately).

What I am looking to display is a histgram that shows:

X-Axis: The summed price

Y-Axis: The number of Names that had a summed price of x in the selected date range on the Y axis

 

So in this example

Date Range: 1/1/2023-1/1/2023, Valid: Yes

Expect bar of height 2 at price 1 because there are two people who have a total price of 1 in that range of time

 

Date Range: 1/1/2023-1/2/2023, Valid: No

Expect to see a bar of height 1 at price 2 and 4.

 

Date Range: 1/1/2023-1/2/2023, Valid: Yes or No

Expect bar of height 1 at price 8, 2, and 1

etc

Need Help
Tutorial Requests
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi cduggan ,

     

    Please try:

    First create a new table for x-axis:

    Summed Price = GENERATESERIES(MIN('Table'[Price]),SUM('Table'[Price]))

    Then apply the measure to the chart:

    Measure = 
var _a = SUMMARIZE('Table','Table'[Name],"Price",SUM('Table'[Price]))
return CALCULATE(COUNT('Table'[Name]),FILTER(_a,[Price]=SELECTEDVALUE('Summed Price'[Price])))

    Final output:

    Best Regards,

    Jianbo Li

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

1 Reply

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi cduggan ,

     

    Please try:

    First create a new table for x-axis:

    Summed Price = GENERATESERIES(MIN('Table'[Price]),SUM('Table'[Price]))

    Then apply the measure to the chart:

    Measure = 
var _a = SUMMARIZE('Table','Table'[Name],"Price",SUM('Table'[Price]))
return CALCULATE(COUNT('Table'[Name]),FILTER(_a,[Price]=SELECTEDVALUE('Summed Price'[Price])))

    Final output:

    Best Regards,

    Jianbo Li

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