cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Percentage of participation

Good day everyone.

I need to ask you for help with a calculated metric that I want to implement in a report.

The objective is to show the total amount of invoices and the percentage of participation they represent over the total, according to the rate that the user chooses.

The tables that would intervene are FACT_TABLE, DIM_TIEMPO, DIM_TARIFA.

I'm telling you an example:

BDIAZCUSI_0-1630070737902.png

For the month of January the total of Invoices is 200mil and the percentage of participation is 100% because it is not filtered by any fee.

Ej2:

BDIAZCUSI_1-1630070807230.png

In this case if the user filters a specific rate (e.g. rate 10), the metric returns the percentage that represents those invoices of tariff 10 on the total invoices of the month (seen in the image above).

The first thing I thought about is putting together the percentage calculation in two parts:

1- first calculating the total invoices, using the REMOVEFILTERS function, so that it ignores the TARIFF filter.

2- create a second metric that does the percentage calculation using the result of the previous metric.

HOWEVER. the REMOVEFILTER would not be working as expected.

TAR_PARTIC_CANT_FACT = CALCULATE(SUM(FACT_TABLE[CANT_FACT]), REMOVEFILTERS(FACT_TABLE[TARIFA]))
Obviously the REMOVEFILTERS is being ignored since the result of this metric returns the same amount as when the rate filter is applied.
Please note: The data model is large and is Direct Query.
If you have any suggestions, I would appreciate them.
1 ACCEPTED SOLUTION
3 REPLIES 3
V-lianl-msft
Community Support
Community Support

Hi @Syndicate_Admin ,

 

Maybe I understand the results you want.You could create a measure like below:

Measure = 
var total = CALCULATE(SUM(financials[ Sales]),ALLSELECTED(financials[Segment]))
return DIVIDE(SUM(financials[ Sales]),total)

Vlianlmsft_0-1630395136342.pngVlianlmsft_1-1630395150158.png

 

I created a sample pbix for your reference.

 

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

Lot of info here... Thank you!.
At the end I used the funcion REMOVEFILTER to be able to remove only ONE filter from the context of the calculation. But it helped me to understand how the ALL() dax function works.
Thank you very much!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors