Forum Discussion
Dax measure need for matrix
PBI Community Good!
What I'm trying to accomplish
Need measures for the sources in my matrix in the screen shots below
I have a sample data set to forward if needed please advise.
Per the schedule below,
the current measure are just place holder to show how I want them displayed in the matrix
Much appreciate your time!
Thank You!
Hi Anonymous ,
We can create measures.
Open Plan = CALCULATE(SUM('Table'[revenue_amount_usd]),FILTER(ALLSELECTED('Table'),'Table'[product_minor]=SELECTEDVALUE('Table'[product_minor]) && 'Table'[source]="Open Plan" && 'Table'[date]=SELECTEDVALUE('Table'[date])))Open Forecast = CALCULATE(SUM('Table'[revenue_amount_usd]),FILTER(ALLSELECTED('Table'),'Table'[product_minor]=SELECTEDVALUE('Table'[product_minor]) && 'Table'[source]="Open Forecast" && 'Table'[date]=SELECTEDVALUE('Table'[date])))total open forecast = SUMX( SUMMARIZE( 'Table', 'Table'[product_minor], 'Table'[date], "total",'Table'[Open Forecast] ), [total] )total open plan = SUMX( SUMMARIZE( 'Table', 'Table'[product_minor], 'Table'[date], "total",'Table'[Open Plan] ), [total] )If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Refer to:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi Anonymous ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create measures.
Open Forecast = CALCULATE(SUM('Table'[revenue_amount_usd]),FILTER(ALLSELECTED('Table'),'Table'[product_minor]=MAX('Table'[product_minor]) && 'Table'[source]="Open Forecast"))Open Plan = CALCULATE(SUM('Table'[revenue_amount_usd]),FILTER(ALLSELECTED('Table'),'Table'[product_minor]=MAX('Table'[product_minor]) && 'Table'[source]="Open Plan"))(3) Then the result is as follows.
Or we can create measures.
_a = SWITCH(TRUE(), MAX('Table'[source])="Open Forecast",CALCULATE(SUM('Table'[revenue_amount_usd]),FILTER(ALLSELECTED('Table'),'Table'[product_minor]=MAX('Table'[product_minor]) && 'Table'[source]="Open Forecast")), MAX('Table'[source])="Open Plan",CALCULATE(SUM('Table'[revenue_amount_usd]),FILTER(ALLSELECTED('Table'),'Table'[product_minor]=MAX('Table'[product_minor]) && 'Table'[source]="Open Plan")))Value = SUMX( SUMMARIZE( 'Table', 'Table'[product_minor], 'Table'[source], "total",'Table'[_a] ), [total] )Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Neeko Tang
I have questions per the following:
<<<<<<<<View B>>>>>
- I got the Dax to work, but my totals for all product are incorrect .
- Not sure if I understood the reason for the =Max in the Dax Measure - Open Forecast 1 circled in red.
- My cards for Open Forecast and Open Plan are showing the max value for Product 3 and not the totals. Also the totals match the max values.
- All the numbers are the same for each year and category in the Matrix underlined in red.
Visualizations details for Matrix <<<View A
Much appreciate your time
Thank you
Hi Anonymous ,
We can create measures.
Open Plan = CALCULATE(SUM('Table'[revenue_amount_usd]),FILTER(ALLSELECTED('Table'),'Table'[product_minor]=SELECTEDVALUE('Table'[product_minor]) && 'Table'[source]="Open Plan" && 'Table'[date]=SELECTEDVALUE('Table'[date])))Open Forecast = CALCULATE(SUM('Table'[revenue_amount_usd]),FILTER(ALLSELECTED('Table'),'Table'[product_minor]=SELECTEDVALUE('Table'[product_minor]) && 'Table'[source]="Open Forecast" && 'Table'[date]=SELECTEDVALUE('Table'[date])))total open forecast = SUMX( SUMMARIZE( 'Table', 'Table'[product_minor], 'Table'[date], "total",'Table'[Open Forecast] ), [total] )total open plan = SUMX( SUMMARIZE( 'Table', 'Table'[product_minor], 'Table'[date], "total",'Table'[Open Plan] ), [total] )If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Refer to:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.