Use Slider selectedvalue in calculation and plot results in circlediagram view

[Aglaerion]

Hi Guys,

I am trying to use the value I select in a slider(ticket price) to calculate a total cost price(nr of kids * ticketprice).
After that I check in which price category the total cost comes into. CAT1, 2 or 3.
Finally I want to show the count of the category in a pie chart view.

I have 2 very simple tables. The first is Person, containing the name of the parent, and the number of kids he has.
The 2nd table is the Slicer table containing the ticket price from 1-10.

The first thing I noticed is that I can not make calulations with the slicer if the column kids is not a measure.
So I created a measure, but it forces me to use SUM, MIN or MAX to do it.
After that I can calculate with the slicer. But that gives at the bottom the result of SUM, MIN or MAX.
I dont want that. Because I want to make a 2nd calculation, and that is the pricecategorie. (CAT1, 2 or 3)
And then I want to count how many parents are in CAT1, how many in CAT2 and how many are in CAT3.
And that result I want to show in my pie chart. This last step I can not manage to do somehow. It only shows one (SUM, MIN or MAX) result from the table in the pie chart view.

Can someone please help me out with this? Below you see a screenshot with my data tables, measures and the pie chart view I like to create. The middle table is on there so you can see what the calculation results are.



 

[Aglaerion]

Tx bro, the solution was that I had indeed create measures for the categories each with the count statement.

I used this :

CAT1 = COUNTX(FILTER('Person',[Category]="CAT1"),[Category])

CAT2 = COUNTX(FILTER('Person',[Category]="CAT2"),[Category])

CAT3 = COUNTX(FILTER('Person',[Category]="CAT3"),[Category])

Then I dragged those into the pie chart.
Tx for the headsup mate. Apreciated!

[Anonymous · Accepted solution]

Hi @Aglaerion ,

Please try the following methods and check if they can solve your problem:

1.Create the simple tables.

2.Select the slicer visual and drag the Ticket price into the slicer.

3.Create a new measure to calculate total. Enter the following DAX formula.

Total Cost = 
VAR SelectedTicketPrice = SELECTEDVALUE(Slicer[Ticket price], 0)
VAR NumberOfKids = SUM(Person[Kids])
RETURN
NumberOfKids * SelectedTicketPrice

4.Drag the Total cost to the visual.

5.Create a new measure to calculate category. Enter the following DAX formula.

Cost Category = 
SWITCH(
    TRUE(),
    [Total Cost] < 20, "CAT1",
    [Total Cost] >= 20 && [Total Cost] < 30, "CAT2",
    [Total Cost] >= 30, "CAT3",
    "OTHER"
)

6.Create three measures for the category.

Category1 = 
CALCULATE(
    COUNTROWS(Person),
    FILTER(
        Person,
        [Cost Category] = "CAT1"
))

Category2 = 
CALCULATE(
    COUNTROWS(Person),
    FILTER(
        Person,
        [Cost Category] = "CAT2"
))

Category3 = 
CALCULATE(
    COUNTROWS(Person),
    FILTER(
        Person,
        [Cost Category] = "CAT3"
))

7.Drag three measures into the pie chart. The result is shown below.

Best Regards,

Wisdom Wu

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