Forum Discussion
Slicer as Calculator
A somewhat self-referencing question here!
There is a PowerBI cost calculator here.
It is a webpage rather than a PowerBi report but I would like to perform the same functions within PBI.
My use case is obviously different but the key similarity is generating a cost based on varying proportions of a 'whole'.
In the example above the two sliders can be independently set anywhere along the slicer and that changes the number of Pro, Frequent and Occasional Users. I can set up a simple slicer but how can I get it to simultaneously adjust 3 parameters?
I can probably get the cost total on the right of the screenshot to work but the slicer setup has me baffled - any help gratefully received!
Thank you
Hi damian_bell
You could try to build a slicer table as below.
Percentage Slicer = GENERATESERIES(0, 1, 0.0001)Build a slicer by between and slicer is as below.
I build a User table.
Measure:
Percent User = VAR _Total_User = 5000 VAR _PercentUser1 = MIN ( 'Percentage Slicer'[Percentage Slicer] ) VAR _PercentUser2 = MAX ( 'Percentage Slicer'[Percentage Slicer] ) - _PercentUser1 VAR _PercentUser3 = 1 - MAX ( 'Percentage Slicer'[Percentage Slicer] ) RETURN SWITCH ( TRUE (), MAX ( User[User] ) = "User1", FORMAT ( _PercentUser1 * _Total_User, "0" ) & " " & FORMAT ( _PercentUser1, "(0.00%)" ), MAX ( User[User] ) = "User2", FORMAT ( _PercentUser2 * _Total_User, "0" ) & " " & FORMAT ( _PercentUser2, "(0.00%)" ), FORMAT ( _PercentUser3 * _Total_User, "0" ) & " " & FORMAT ( _PercentUser3, "(0.00%)" ) )TotalPrice = VAR _Total_User = 5000 VAR _PercentUser1 = MIN ( 'Percentage Slicer'[Percentage Slicer] ) VAR _PercentUser2 = MAX ( 'Percentage Slicer'[Percentage Slicer] ) - _PercentUser1 VAR _PercentUser3 = 1 - MAX ( 'Percentage Slicer'[Percentage Slicer] ) VAR _NumUser1 = _PercentUser1*_Total_User VAR _NumUser2 = _PercentUser2*_Total_User VAR _NumUser3 = _PercentUser3*_Total_User VAR _TotalPrice = _NumUser1*100+_NumUser2*200+_NumUser3*50 RETURN _TotalPriceResult is as below.
You can download the pbix file from this link: File
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi damian_bell
You could try to build a slicer table as below.
Percentage Slicer = GENERATESERIES(0, 1, 0.0001)Build a slicer by between and slicer is as below.
I build a User table.
Measure:
Percent User = VAR _Total_User = 5000 VAR _PercentUser1 = MIN ( 'Percentage Slicer'[Percentage Slicer] ) VAR _PercentUser2 = MAX ( 'Percentage Slicer'[Percentage Slicer] ) - _PercentUser1 VAR _PercentUser3 = 1 - MAX ( 'Percentage Slicer'[Percentage Slicer] ) RETURN SWITCH ( TRUE (), MAX ( User[User] ) = "User1", FORMAT ( _PercentUser1 * _Total_User, "0" ) & " " & FORMAT ( _PercentUser1, "(0.00%)" ), MAX ( User[User] ) = "User2", FORMAT ( _PercentUser2 * _Total_User, "0" ) & " " & FORMAT ( _PercentUser2, "(0.00%)" ), FORMAT ( _PercentUser3 * _Total_User, "0" ) & " " & FORMAT ( _PercentUser3, "(0.00%)" ) )TotalPrice = VAR _Total_User = 5000 VAR _PercentUser1 = MIN ( 'Percentage Slicer'[Percentage Slicer] ) VAR _PercentUser2 = MAX ( 'Percentage Slicer'[Percentage Slicer] ) - _PercentUser1 VAR _PercentUser3 = 1 - MAX ( 'Percentage Slicer'[Percentage Slicer] ) VAR _NumUser1 = _PercentUser1*_Total_User VAR _NumUser2 = _PercentUser2*_Total_User VAR _NumUser3 = _PercentUser3*_Total_User VAR _TotalPrice = _NumUser1*100+_NumUser2*200+_NumUser3*50 RETURN _TotalPriceResult is as below.
You can download the pbix file from this link: File
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is *exactly* what I needed! Thank you so much, it would have taken me many frustrating hours to get even close to working this out. Now I see your solution I can see that it isn't too complicated but I have learnt a lot as I have played with this today. I am very grateful for your help. Thank you.
Greetings, I hope this information is useful for you to implement a time range in the user table.
Calculate the time range**
Now, you can calculate the duration of usage for each user based on the start and current dates.In Power BI, you can use the DAX formula 'DATEDIFF' to calculate the difference between two dates:
'''DAX
Duracion_Dias = DATEDIFF([Start Date], [Current Date], DAY)
```This will add an additional column that shows the number of days between the start date and the current date.
| User | Mail | Role | Usage (%) | Start Date | Current Date | Duration (days) |
|-----------|----------------------|-------------|---------|-----------------|--------------|----------------|
| John | [email protected] | Administrator | 85 | 01-01-2024 | 10-12-2024 | 344 |
| Mary | [email protected] | User | 40 | 15-02-2024 | 10-12-2024 | 299 |
| Carlos | [email protected] | User | 60 | 01-03-2024 | 10-12-2024 | 285 |
| Ana | [email protected] | Contributor | 75 | 20-04-2024 | 10-12-2024 | 234 |
| Sofia | [email protected] | Administrator | 90 | 05-05-2024 | 10-12-2024 | 219 |#### 3. **Analyze usage over time**
With these new columns, you can create visualizations in Power BI that show how your use of products or systems varies over time. For example:
- Line Charts: To show cumulative or daily usage over time.
- **Histograms**: To see the distribution of the duration of use among users.
- **Cross-tables**: To combine different metrics and get a more complete view of usage behavior.
