topic Power pivot dynamic rows does not calculate percent of total correct in DAX Commands and Tips

Power pivot dynamic rows does not calculate percent of total correct

BaldAccountant — Thu, 06 Jul 2023 20:14:44 GMT

In Powerpivot I have am using measures to dynamically choose the rows. For example if a user wants to change the rows from Colors to Department or State they don't have to restructure the pivot table, they can just choose the option they want for the rows either in the pivot table filter or in a slicer.

I am having problems with percents of the total like X% of total sales. they all come up as 100%, but other percents are OK like the % of a category that is red.

I will try to post my test data but I have been having trouble with that. If the link does not work I will try to put as much info on the structure of the power pivot in this post. https://www.dropbox.com/scl/fi/6upwbbraglrm5awtkdhey/Test-all.xlsx?rlkey=i0u5u57fkhuv1t6wlye63hkgb&dl=0

here is the formula I am using

Percent$2:=var filter1 = filter(Table1,contains(values(Table2[Group Value]),Table2[Group Value],Table1[Color])||CONTAINS(VALUES(Table2[Group Value]),Table2[Group Value],Table1[Dept])||CONTAINS(VALUES(Table2[Group Value]),Table2[Group Value],Table1[State]))

return

CALCULATE([Percent$],filter1)

I have a table for grouping as seen below:

Here is another dynamic formula that works:

Percent Red2:=var filter1 = filter(Table1,CONTAINS(VALUES(Table2[Group Value]),Table2[Group Value],Table1[Color])||CONTAINS(VALUES(Table2[Group Value]),Table2[Group Value],Table1[Dept])||CONTAINS(VALUES(Table2[Group Value]),Table2[Group Value],Table1[State]))

return

CALCULATE([Percent Red],filter1)

I hope the link works, sorry if it does not. it works for me when I click on it.

Re: Power pivot dynamic rows does not calculate percent of total correct

Anonymous — Mon, 10 Jul 2023 03:17:11 GMT

Hi BaldAccountant ,

You could use unpivot columns in power query, select three columns and click 'Unpivot columns'.

Then you can calculate the value group by Attribute and Value columns, below is an example.

Measure = CALCULATE(SUM('Table'[Dollars]),FILTER(ALLSELECTED('Table'),[Attribute]=MAX('Table'[Attribute])&&[Value]=MAX('Table'[Value])))

Or you can try the field parameters.

Let report readers use field parameters to change visuals (preview) - Power BI | Microsoft Learn

Best Regards,

Stephen Tao

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

Re: Power pivot dynamic rows does not calculate percent of total correct

BaldAccountant — Mon, 10 Jul 2023 15:13:46 GMT

Anonymous

I am not 100% sure we are talking about the same question, but I will try your solution.

How do I go to power query in power pivot?

Re: Power pivot dynamic rows does not calculate percent of total correct

Anonymous — Wed, 12 Jul 2023 03:21:15 GMT

Hi BaldAccountant ,

In Power BI Desktop, click "Transform data" to go to Power Query.

Best Regards,

Stephen Tao

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

Re: Power pivot dynamic rows does not calculate percent of total correct

BaldAccountant — Tue, 18 Jul 2023 02:28:38 GMT

Anonymous

You can open Power Query through the Power Query Editor. You can open Power Query Editor by following these steps:

Select the Data tab.

In the Get and Transform Data group, select Lunch Power Query Editor.

Your use of Power Pivot and use of Power Query are not in conflict. You first get the data source Excel in Power Query, unpivot the columns, click Close & Load, and the data will be reapplied to Excel.

After above, use Power Pivot to create your DAX.

Best Regards,

Stephen Tao

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