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.
I'm trying to calculate the % of waste distribution type for every month year so for example in the above image, I want a measure to calculate the % of every month so it should be like for Jan 2021 the % of Closed loop recycling =( 7/(7+15+24))*100 and same for landfill and open loop recycling. I want to display the % of waste distribution in a line chart against the date to get a trend of how much is he % of waste distrbution for every type in every month and year.
How do I create a measure for this?
Hi @isha_pote ,
Please have a try.
closed loop recying_mea =
VAR _1 =
CALCULATE (
COUNT ( table[waste distribution type] ),
FILTER (
ALL ( table ),
table[date] = SELECTEDVALUE ( table[date] )
&& table[waste distribution type] = "Closed loop recyling"
)
)
VAR _all =
CALCULATE (
COUNT ( table[waste distribution type] ),
FILTER ( ALL ( table ), table[date] = SELECTEDVALUE ( table[date] ) )
)
RETURN
( _1 / _all ) * 100
Other calculations similar to this one.
Or you can use a column.
closed loop recying_col =
VAR _1 =
CALCULATE (
COUNT ( table[waste distribution type] ),
FILTER (
table,
table[date] = EARLIER ( table[date] )
&& table[waste distribution type] = "Closed loop recyling"
)
)
VAR _all =
CALCULATE (
COUNT ( table[waste distribution type] ),
FILTER ( table, table[date] = EARLIER ( table[date] ) )
)
RETURN
( _1 / _all ) * 100
Or you want the following output:
2021/1/1 | closed... | 7 | (7/46)*100 |
2021/1/1 | land... | 15 | (15/46)*100 |
2021/1/1 | open... | 24 | 24/46)*100 |
Have a try.
diff_mea =
VAR _1 =
CALCULATE (
COUNT ( table[waste distribution type] ),
FILTER (
all(table),
table[date] = selectedvalue ( table[date] )
&& table[waste distribution type] = selectedvalue(table[waste distribution type])
)
)
VAR _all =
CALCULATE (
COUNT ( table[waste distribution type] ),
FILTER ( all(table), table[date] = selectedvalue ( table[date] ) )
)
RETURN
( _1 / _all ) * 100
diff_col =
VAR _1 =
CALCULATE (
COUNT ( table[waste distribution type] ),
FILTER (
(table),
table[date] = EARLIER ( table[date] )
&& table[waste distribution type] = earlier(table[waste distribution type])
)
)
VAR _all =
CALCULATE (
COUNT ( table[waste distribution type] ),
FILTER ( (table), table[date] = earlier ( table[date] ) )
)
RETURN
( _1 / _all ) * 100
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
With the above formula it's showing an error for && waste data(3) waste distribution type portion. Can you help with this?
Hi @isha_pote ,
Please have a try.
diff_m =
diff_mea
=
VAR _1 =
CALCULATE (
COUNT ( table[waste distribution type] ),
FILTER ( ALL ( table ), table[date] = SELECTEDVALUE ( table[date] ) ),
FILTER (
ALL ( wasteTABLE ),
table[waste distribution type] = SELECTEDVALUE ( table[waste distributiontype] )
)
)
VAR _all =
CALCULATE (
COUNT ( table[waste distribution type] ),
FILTER ( ALL ( table ), table[date] = SELECTEDVALUE ( table[date] ) )
)
RETURN
( _1 / _all ) * 100
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |