Forum Discussion

b2wise's avatar
b2wise
Helper III
2 years ago
Solved

Pivot a Table Visual (DAX Measure Concept Help)

Hi all,   I find myself many times putting together a table visual in Power BI and then exporting it to Excel so I can pivot and find some more insights. Instead of exporting to excel I'd like to s...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file if it suits your requirement.

     

     

     

     

    Sales measure: = 
SUM( Sales[Sales] )

     

    distinct product sold: = 
COUNTROWS(SUMMARIZE(Sales, 'Calendar'[Date]))

     

    expected result measure: = 
SUMX (
    FILTER (
        SUMMARIZE ( Sales, Category[Product Catetory] ),
        [distinct product sold:]
            = SELECTEDVALUE ( 'Product Sold distinct count CT'[Count] )
    ),
    [Sales measure:]
)

     

    b2wise.pbix30 KB
b2wise's avatar
b2wise
Helper III
2 years ago

You, sir, are a kind and generous genius! Thank you very much!

 

If you have patience, I have a couple more questions on the expected result measure:

 

  1. How do I add a filter using a measure such as average sales price > 30?
  2. I don't feel like my original problem is a very common one. What are other ways to solve the same problem? I'm thinking I could have created my table visual as a regular table in Power Query though it would be pretty difficult to calculate all those DAX measures.

    In short, you have answered my question and I am very grateful!

Thank you again