Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PBI-Curious
Resolver I
Resolver I

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

 

PBICurious_0-1684956251553.png

 

 

PBICurious_1-1684956287358.png

 

Much appreciate your time!

 

Thank You!

 

1 ACCEPTED SOLUTION

Hi @PBI-Curious ,

 

vtangjiemsft_0-1685346735650.png

 

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]

)

vtangjiemsft_1-1685346774666.png

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. 

View solution in original post

7 REPLIES 7
PBI-Curious
Resolver I
Resolver I

Hello Tang

Per your solution above, I got the measure to work.

Much appreciate your time and help!

Thank you

PBI-Curious
Resolver I
Resolver I

 Hello Tang 

Good Day!

Did you receive the link to my sample.pbix sent to you via a private message?

 

PBI-Curious
Resolver I
Resolver I

Hello Neeko Tang

 

Per your example, I working on it now, but I'm getting an error message in the Dax.

How did you upload your sample .pbix? 

I have a sample .pbix I can upload the same way for your review.

Please advise 

Thank you

PBICurious_0-1685126822045.png

 

v-tangjie-msft
Community Support
Community Support

Hi @PBI-Curious ,

 

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

vtangjiemsft_0-1685069892475.png

(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.

vtangjiemsft_1-1685069947462.png

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]

)

vtangjiemsft_0-1685071216121.png

 

 

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>>>>>

  1. I got the Dax to work, but my totals for all product are incorrect .
  2.  Not sure if I understood the reason for the =Max in the Dax Measure - Open Forecast 1 circled in red.
  3. 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.
  4. All the numbers are the same for each year and category in the Matrix underlined in red.

 

 

PBICurious_1-1685321105577.png

 

Visualizations details for Matrix <<<View A

PBICurious_2-1685321355071.png

Much appreciate your time

Thank you

Hi @PBI-Curious ,

 

vtangjiemsft_0-1685346735650.png

 

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]

)

vtangjiemsft_1-1685346774666.png

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. 

Hello Tang

I sent you a private message with an sample.pbix.

I will take a look at your post above. 

Much appreciate your time!

Thank you

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors