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 there
I have a table like this
Product, date, ind1, ind2
Indicators ind1 and ind2 are integers
I have done a matrix like this
Rows : Date
Columns : nothing
Values : ind1 (aggregated by Sum), ind2 (aggregated by Sum)
It works fine, but I need to add à third indicator in the values field which will be Sum(ind2)/Sum(ind1) ir order to get the percentage
I did not find the way to do it
Do you have any idea ?
Thanks for your help
Regards
Marc
Solved! Go to Solution.
Percentage = Calculate(Divide(Sum(ind2), Sum(ind1)))
Percentage = Calculate(Divide(Sum(ind2), Sum(ind1)))
Thank you for your answer and sorry for this late follow-up
Unfortunately, it doesn't work
He is a sample to explain more clearly my need
Market
Item | Date | On | Fault | |
A1 | ItemA | 01/11/2017 | 1 | 0 |
A1 | ItemA | 02/11/2017 | 0 | 0 |
A1 | ItemA | 03/11/2017 | 1 | 1 |
A1 | ItemB | 01/11/2017 | 1 | 1 |
A1 | ItemB | 02/11/2017 | 0 | 0 |
A1 | ItemB | 03/11/2017 | 1 | 1 |
If i make a pivot table group by market :
A1 | ||
Date | Sum of On | Sum of Fault |
01/11/2017 | 2 | 1 |
02/11/2017 | 0 | 0 |
03/11/2017 | 2 | 2 |
My need is to make the ratio :"sum of Fault"/"sum of On"
A1 | |||
Date | Sum of On | Sum of Fault | ratio |
01/11/2017 | 2 | 1 | 0,5 |
02/11/2017 | 0 | 0 | #DIV/0! |
03/11/2017 | 2 | 2 | 1 |
As you can see, the ratio is calculated after the sum aggregation
I don(t know how to do this in Power BI (and in Excel) is one pass
Thanks for your help
Regards
Marc
Hi Marc,
I think I may be getting confused here somewhere, is the third table in your example your expected outcome? If so the expression I gave you will work in power bi. I used the example data set you gave and got the following output:
Thanks for this so quick answer.
I'm confused too
Your formula works as a measure :
But for some reason, with my real datas, I can't make a measure, only a column. With a column, obviously, it doesn't work.
I have no idea why i can't make a measure, my real table has same data types than the sample one.
Would you have an idea ?
Regards
Marc
Hi again
I tried this :
Copy the formula working as a column
Paste the formula as a measure and it worked...
If I try to type the formula in a new measure, auto type of the fields does not show the fields I need. Very curious.
Whatsoever, your solution was the right one and I'm very grateful
Regards
Marc
What are you using as your data source?
an ODBC data source (pointing to SQlite) with transformations in power query
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 |
---|---|
109 | |
97 | |
80 | |
67 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |