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
Anonymous
Not applicable

RE: SUMMARIZE AND SUM

Hi,

 

Good day.

 

I am making a report again and I do have a problem creating a 'pivot-table-look-a-like. Below is 2 tables i need to create using DAX. See the data and expected result.

 

TABLE 1.:

 

From This:

Orig Table (From)Orig Table (From)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Into this: (Summary) - all audited amount was placed to each status

 

Summary of Orig Table (A)Summary of Orig Table (A)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Table 2:

 

FROM the outcome above - (Summary)..

 

Into this: (Current Status Table) - this is to placed to proper status to match with the original amount. Please note that the total audited amount must not exceed the orig amount of the document code. 

 

Current Status Table (B)Current Status Table (B)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Note 1: Please be guided that the outcome on the Table Scenario#2 are what I really want to achieve.

Note 2: Each status prevails to other status if only has the same audited amount under the same doc code.

    a. For Reversal - prevails OVER all

    b. Closed Inact - prevails over In Progress, Closed-FYI Only, Issue Closed

    c. In Progress - prevails over FYI and Issue Closed

    d. Closed - FYI Only prevails over issue closed

 

I hope you get it. And I hope you can help on this. Should you need more clarification please let me know.

 

TIA and stay safe.

 

🙂

1 REPLY 1
Washivale
Resolver V
Resolver V

Hi @Anonymous ,

 

if you have first table as your raw table, you would just use Matrix visual, with below configurations.

 

Row Section - Doc Type, Department

Column Section - Current Status

Values- Sum of Original Amount

 

Other Option is to use Pivot Option from Power Query, by Selecting Current Status as Pivot column.

 

Hope this helps.

 

Regards,

-Washivale 

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