Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am wondering if this is currenly possible in Power BI.
I have a table with columns: month, client, sales. I am adding a new column with total sale for month and then divide sale / total sale per month to get share.
What i want to do now is to make this dynamic so that i can choose several clients only and calculate the total sale for month only for those clients. I can do that by filtering clients in the source sql but this requires active connection to the database to change my calculation.
Is there any way to do such filtering without a need of refreshing the data from the data source?
Thanks
Solved! Go to Solution.
@RafalK When you have created new column and applied formula of sales / total sales per month it would have calculated that based on values in each row. Therefore on report designer if you drop in slicer and select only few clients you should get share values only for those clients as from your table values only from those rows would be returned. Hope this make sense
Thanks for the answer but this will not fix my problem.
If i use a slicer, i will get shares only for the clients i want but those shares will still be the same. I want the total and therfore the share value to change when i select just a few clients.
Example:
I have 3 clients: A with sale 100$, B with 50$ and C with 20$. Together that gives me 170$ total and a share of:
A: 100/170 = 59%
B = 50/170 = 30%
C = 20/170 = 11%
Now if i select only clients B and C i don't want to get 0,30% and 0,11% but:
B = 50/(50+20) = 71%
C = 20/(50+20) = 29%
Right now this is all done using DAX, where i calculate Sales[Value] / SUM(Sales[Value])
Hi Rafal,
You need to create this calculated measure and not column:
DIVIDE(SUM( Sales[Value] ) ,
CALCULATE(
SUM( Sales[Value] ),
ALLSELECTED( Sales[Client] )
))
Or Client[Name] instead of Sales[Client] depending on how your tables are contructed and related.
Tell me how it goes.
Thanks guys!!!!
Problem fixed
Sounds like you want to use a Slicer visualization. You can select multiple items in a slicer. You can also use Page, Report and Visual level filters.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |